db2嵌套json_大数据知识库

我尝试使用db2json功能，特别是嵌套表。

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                            
                       }                                                
                     ]                                               
            }                                                  
    }'
));

这可以很好地工作，但是很明显数组中的项是硬编码的引用。

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
;

以下是我正在努力实现的目标：

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）：

-- 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<>小提琴示例。

更新

创建适用于任何json数组的通用函数非常方便：

-- 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
@

这种通用函数简化了解决方案：

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

1条答案

更新

相关问题

热门标签

最新问答