Question

将JSON的档案转换成一栏,但一栏Im无法读到,其价值并不大。

CREATE OR REPLACE TEMPORARY TABLE SAMPLE_J
(
SAMPLE_JSON VARCHAR(100)
);

INSERT INTO SAMPLE_J
(SAMPLE_JSON)
VALUES ( 页: 1"ident":["Product:Tires","sender_id:12345","sender_name:John Who","Tire_Condition:New"注iii 

--this is my query
SELECT 
   SAMPLE_JSON,
   JSON_EXTRACT_PATH_TEXT(SAMPLE_JSON, Product ) as test1,
   JSON_EXTRACT_PATH_TEXT(SAMPLE_JSON, Product[0注.Product ) as test2
FROM SAMPLE_J;

the result is null for test 1 and test 2

这就是JSON档案如何看待:

页: 1

“身份”:

"Product:Tires",

"sender_id:12345",

"sender_name:John Who",

"Tire_Condition:New"

注

iii

Answer 1

您在此是关键<代码> 识别内的一个阵列。

with data as (
    select $1 as j
    from VALUES ( {"ident":["Product:Tires","sender_id:12345","sender_name:John Who","Tire_Condition:New"]} ) 
)

select j
    , json_extract_path_text(j,  ident ) j_ident
    , json_extract_path_text(j,  ident[0] ) j_ident0
from data;

<代码>j_ident0是该阵列的第一个要素,其价值为。产品:Tires。这并不是一位神职人员,只是一个扼杀。

友情链接