1

How could I extract nested json using sqlite json-extract or other sqlite json command ?

Here I'd like to extract given_id

"invoices": [{

........


    "items": [{


    "given_id": "TBC0003B",


    ...


        }


    ]

   }

]

Thanks.

oz123
  • 27,559
  • 27
  • 125
  • 187
kkk
  • 95
  • 1
  • 2
  • 11
  • see this link: https://stackoverflow.com/questions/8481380/is-there-a-json-equivalent-of-xquery-xpath – lastboy Jan 04 '20 at 19:33

1 Answers1

0

In SQLite you can use json_extract() as follows:

select json_extract(my_json_col, '$.invoices[0].items[0].given_id') my_given_id from mytable

This gives you the given_id attribute of the first element of the items array under first element of the invoices array.

Demo on DB Fiddle:

with mytable as (select '{
    "invoices": [{
        "items": [{ "given_id": "TBC0003B" }] 
    }]
}' my_json_col)
select json_extract(my_json_col, '$.invoices[0].items[0].given_id') my_given_id from mytable

| my_given_id |
| :---------- |
| TBC0003B    |
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Thanks so much, it's nested json array and I couldn't know the length of the array. I need to get all **given_id** value for dynamic size of this nested json. How could I achieve it ? – kkk Jan 04 '20 at 19:51
  • 1
    @kkk: then we would probably need a json table-valued function. The exact query would depend on the structure of the array, and on the result that you expect. You would need to provide this information: a valid and complete json document (no `...`s), and the expected result as tabular text. – GMB Jan 04 '20 at 20:02
  • could you please show with example how to do it ? thanks – kkk Jan 05 '20 at 02:14