Get value from JSON Array List in DB2

Question

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

Answer 1

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"