Db2 嵌套 JSON [英] Db2 nested JSON
本文介绍了Db2 嵌套 JSON的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在尝试使用 Db2 JSON 功能，尤其是嵌套表.
I am trying to use Db2 JSON capabilities and in particular nested tables.
CREATE TABLE JSON.TEST1 (COL1 VARBINARY(2000));
INSERT INTO JSON.TEST1 (COL1) VALUES (JSON_TO_BSON(
'{"id" : 103,
"orderDate": "2014-06-20",
"items": {
"item": [ { "partNum": "872-AA",
"productName": "Lawnmower",
"quantity": 1,
"USPrice": 749.99
},
{ "partNum": "837-CM",
"productName": "Digital Camera",
"quantity": 2,
"USPrice": 199.99
}
]
}
}'
));
这很好用，但显然数组中的项目是硬编码的引用.
This works fine, however obviously items in the array are hardcoded references.
SELECT id
,orderDate
,product1
,product2
FROM json.TEST1 AS js,
JSON_TABLE
(js.COL1, 'strict $'
COLUMNS( id INTEGER PATH '$.id'
,orderDate DATE PATH '$.orderDate'
,product1 VARCHAR(32) PATH '$.items.item[0].productName'
,product2 VARCHAR(32) PATH '$.items.item[1].productName'
)
ERROR ON ERROR) AS t
;
以下是我正在尝试的工作:
The following is what I am trying to get working:
SELECT id
,orderDate
,productName
FROM json.TEST1 AS js,
JSON_TABLE
(js.COL1, '$'
COLUMNS( id INTEGER PATH '$.id'
,orderDate DATE PATH '$.orderDate'
,NESTED 'lax $.items.item[]'
COLUMNS (
"productName" VARCHAR(32)
)
)
) as t;
作为参考我收到的错误
1) [Code: -104, SQL State: 42601] An unexpected token "'lax $.items.item[]'
COLUMNS (
" was found following ",NESTED". Expected tokens may include: "<space>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.26.14
2) [Code: -727, SQL State: 56098] An error occurred during implicit system action type "2". Information returned for the error includes SQLCODE "-104", SQLSTATE "42601" and message tokens "'lax $.items.item[]'
COLUMNS (
|,N".. SQLCODE=-727, SQLSTATE=56098, DRIVER=4.26.14
推荐答案
很遗憾，您必须自己取消嵌套 JSON 数组，例如使用递归公用表表达式 (RCTE):
Unfortunately, you must unnest JSON arrays on your own, for example, with Recursive Common Table Expression (RCTE):
-- A table with JSON documents
WITH TAB (DOC_ID, DOC) AS
(
VALUES
(
1,
'{"id" : 103,
"orderDate": "2014-06-20",
"items": {
"item": [ { "partNum": "872-AA",
"productName": "Lawnmower",
"quantity": 1,
"USPrice": 749.99
},
{ "partNum": "837-CM",
"productName": "Digital Camera",
"quantity": 2,
"USPrice": 199.99
}
]
}
}'
)
)
-- get a JSON array only for each record
, ITEMS_ARRAY (DOC_ID, ITEMS) AS
(
SELECT DOC_ID, JSON_OBJECT(KEY 'items' VALUE JSON_QUERY(DOC, '$.items.item') FORMAT JSON)
FROM TAB
)
-- Use RCTE to unnest it
, ITEMS (DOC_ID, INDEX, ITEM) AS
(
SELECT DOC_ID, 0, JSON_QUERY(ITEMS, '$.items[0]')
FROM ITEMS_ARRAY
WHERE JSON_EXISTS(ITEMS, '$.items[0]')
UNION ALL
SELECT I.DOC_ID, I.INDEX+1, JSON_QUERY(A.ITEMS, '$.items['|| TRIM(I.INDEX+1) ||']')
FROM ITEMS I, ITEMS_ARRAY A
WHERE I.DOC_ID = A.DOC_ID AND JSON_EXISTS(A.ITEMS, '$.items['|| TRIM(I.INDEX+1) ||']')
)
SELECT D.*, IT.*
--, I.*
FROM TAB T
JOIN ITEMS I ON I.DOC_ID = T.DOC_ID
-- array element to row
CROSS JOIN JSON_TABLE
(
I.ITEM, 'strict $' COLUMNS
(
PARTNUM VARCHAR(20) PATH '$.partNum'
, PRODCUCTNAME VARCHAR(20) PATH '$.productName'
, QUANTITY INT PATH '$.quantity'
, USPRICE DECFLOAT PATH '$.USPrice'
) ERROR ON ERROR
) IT
-- other elements of original JSON to row
CROSS JOIN JSON_TABLE
(
T.DOC, 'strict $' COLUMNS
(
ID INT PATH '$.id'
, ORDERDATE DATE PATH '$.orderDate'
) ERROR ON ERROR
) D
;
结果是:
|ID |ORDERDATE |PARTNUM|PRODCUCTNAME |QUANTITY|USPRICE|
|---|----------|-------|--------------|--------|-------|
|103|2014-06-20|872-AA |Lawnmower |1 |749.99 |
|103|2014-06-20|837-CM |Digital Camera|2 |199.99 |
db<>fiddle 示例.
创建适用于任何 JSON 数组的泛型函数很方便:
It's convenient to create a generic function suitable for any JSON array:
-- WITH A GENERIC TABLE FUNCTION
CREATE OR REPLACE FUNCTION UNNEST_JSON (P_DOC CLOB(1M), P_PATH VARCHAR(128))
RETURNS TABLE
(
INDEX INT
, ITEM CLOB(1M)
)
RETURN
WITH ITEMS_ARRAY (ITEMS) AS
(
VALUES JSON_OBJECT(KEY 'items' VALUE JSON_QUERY(P_DOC, P_PATH) FORMAT JSON)
)
, ITEMS (INDEX, ITEM) AS
(
SELECT 0, JSON_QUERY(ITEMS, '$.items[0]')
FROM ITEMS_ARRAY
WHERE JSON_EXISTS(ITEMS, '$.items[0]')
UNION ALL
SELECT I.INDEX+1, JSON_QUERY(A.ITEMS, '$.items['|| TRIM(I.INDEX+1) ||']')
FROM ITEMS I, ITEMS_ARRAY A
WHERE JSON_EXISTS(A.ITEMS, '$.items['|| TRIM(I.INDEX+1) ||']')
)
SELECT INDEX, ITEM
FROM ITEMS
@
这样的泛型函数简化了解决方案:
Such a generic function simplifies the solution:
WITH TAB (DOC_ID, DOC) AS
(
VALUES
(
1,
'{"id" : 103,
"orderDate": "2014-06-20",
"items": {
"item": [ { "partNum": "872-AA",
"productName": "Lawnmower",
"quantity": 1,
"USPrice": 749.99
},
{ "partNum": "837-CM",
"productName": "Digital Camera",
"quantity": 2,
"USPrice": 199.99
}
]
}
}'
)
,
(
2,
'{"id" : 203,
"orderDate": "2014-06-20",
"items": {
"item": [ { "partNum": "002-AA",
"productName": "Lawnmower",
"quantity": 10,
"USPrice": 749.99
},
{ "partNum": "002-BB",
"productName": "Digital Camera",
"quantity": 20,
"USPrice": 199.99
}
]
}
}'
)
)
SELECT T.DOC_ID, A.INDEX, D.*, IT.*
FROM
TAB T
-- unnesting
, TABLE(UNNEST_JSON(T.DOC, '$.items.item')) A
-- array element to row
, JSON_TABLE
(
A.ITEM, 'strict $' COLUMNS
(
PARTNUM VARCHAR(20) PATH '$.partNum'
, PRODCUCTNAME VARCHAR(20) PATH '$.productName'
, QUANTITY INT PATH '$.quantity'
, USPRICE DECFLOAT PATH '$.USPrice'
) ERROR ON ERROR
) IT
-- other elements of original JSON to row
, JSON_TABLE
(
T.DOC, 'strict $' COLUMNS
(
ID INT PATH '$.id'
, ORDERDATE DATE PATH '$.orderDate'
) ERROR ON ERROR
) D;
结果是:
|DOC_ID|INDEX|ID |ORDERDATE |PARTNUM|PRODCUCTNAME |QUANTITY|USPRICE|
|------|-----|---|----------|-------|--------------|--------|-------|
|1 |0 |103|2014-06-20|872-AA |Lawnmower |1 |749.990|
|1 |1 |103|2014-06-20|837-CM |Digital Camera|2 |199.990|
|2 |0 |203|2014-06-20|002-AA |Lawnmower |10 |749.990|
|2 |1 |203|2014-06-20|002-BB |Digital Camera|20 |199.990|
这篇关于Db2 嵌套 JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
