Below is example kind of data. Is there any way to get value only id from below json array.
Env: DB2 query TABLE NAME: RTN_PRD COLUMN NAME : DTL_PRD
{"ProductList":"[{\"ID\":\"1111\",\"Product\":\"A001\"} ,{\"ID\":\"222\",\"Product\":\"A002\"} ,{\"ID\":\"333\",\"Product\":\"A003\"}]"}
I have tried below solution, but error.
SELECT
P.ID_PROD
FROM
RTN_PRD,
JSON_TABLE
( JSON_VALUE ( DTL_PRD,'$.ProductList[*]' )
COLUMNS (
ID_PROD varchar (100) PATH '$.ID')
) P
DB2 for LUW solution.
Using one of the generic UDFs provided here.
WITH RTN_PRD (DTL_PRD) AS
(
VALUES '{"ProductList":
[
{"ID":"111", "Product":"A001"}
, {"ID":"222", "Product":"A002"}
, {"ID":"333", "Product":"A003"}
]
}'
)
SELECT T.ITEM, JSON_QUERY (T.ITEM, '$.ID') AS ID
FROM
RTN_PRD R
, TABLE (UNNEST_JSON (R.DTL_PRD, '$.ProductList')) T
The result is:
| ITEM | ID |
|---|---|
| "{ "ID" : "111", "Product" : "A001" }" | "111" |
| "{ "ID" : "222", "Product" : "A002" }" | "222" |
| "{ "ID" : "333", "Product" : "A003" }" | "333" |
'strict $',error on error) are missing, but DB2LUW does not provide a simple way to extract data from JSON arrays. You can find a solution here. If you are using DB2 for Z/OS maybe this will work too. With DB2 for IBMi you can use nested path clause