Как разбрать JSON колонку?

Question

Имею следующую таблицу с вложенными JSON:

id   |  json
345  [{"items":[{"type":"item","_id":3435,"type_id":0908,"are":""}],"contr":"100"},
      {"items":[{"type":"item","_id":3435,"type_id":0908,"are":""}],"contr":"900"}]  
234  []
235  [{"items":[{"type":"item","_id":3435,"type_id":0908,"are":""}],"contr":"100"}]]

Просьба, помочь разобрать данное вложение. Вложение может иметь как ноль items [], так и множество 1-N. Необходимо получить _id и type_id в отдельных колонках.

Ожидаю получить подобный вид:

 id  | 1items  | 1_items | 2items | 2_items  |     contr
345      3435       0908     3435      0908          100
234      null       null     null      null         null
235      3435       0908     null      null          100

Answer 1

Как-то так:

SELECT test.id, 
       JSON_VALUE(test.jsondata, '$[0].items[0]._id') item1_id, 
       JSON_VALUE(test.jsondata, '$[0].items[0].type_id') item1_type_id, 
       JSON_VALUE(test.jsondata, '$[0].items[1]._id') item2_id, 
       JSON_VALUE(test.jsondata, '$[0].items[1].type_id') item2_type_id, 
       JSON_VALUE(test.jsondata, '$[0].contr') contr
FROM test

Или так:

SELECT test.id,
       jsontable.*
FROM test
CROSS JOIN JSON_TABLE(test.jsondata,
                      '$[*]' COLUMNS ( row_number FOR ORDINALITY,
                                       id INT PATH '$.items[0]._id',
                                       type_id INT PATH '$.items[0].type_id',
                                       contr VARCHAR2(10) PATH '$.contr' )) jsontable

https://dbfiddle.uk/?rdbms=oracle_18&fiddle=7a4d0bed4f58c4221a3ece744f74e663

Answer 2

Просто перечислите нужные колонки (на SQL Live):

select 
    jd.doc.list[0].items."_id"   itemid1, 
    jd.doc.list[0].items.type_id typeid1,
    jd.doc.list[0].contr         contr1,
    jd.doc.list[1].items."_id"   itemid2, 
    jd.doc.list[1].items.type_id typeid2,
    jd.doc.list[1].contr         contr2
from (
    select treat ('{"list":'||jd.doc||'}' as json) doc
    from jsondoc jd) jd

И получите ожидаемый результат:

ITEMID1  TYPEID1  CONTR1   ITEMID2  TYPEID2  CONTR2  
-------- -------- -------- -------- -------- --------
3435     908      100      3436     908      900     
null     null     null     null     null     null    
3437     908      100      null     null     null

БЛОГ НА HUSL

Как разбрать JSON колонку?

Ответы (2 шт):