CODEWITHSUNDEEP
jsonsqlitenestedjson-extractsqlite-json1
kkk
kkk

Reputation: 115

how to extract nested json using sqlite json-extract

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.

Upvotes: 1

Views: 1357

Answers (1)

GMB
GMB

Reputation: 222652

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    |

Upvotes: 1

Related Questions