One of my columns contains the below array. I just need the order_lookup_code. How do I do this?
| vendor_provided_data |
|---|
| {"vendor_data": [{"order_id": 10000, "shipment_id": 20000, "order_lookup_code": "fr_30000dd"}]} |
-
If the array contains multiple elements, do you want multiple rows in the result?user330315– user3303152021-05-13 05:38:44 +00:00Commented May 13, 2021 at 5:38
-
No. Preferably just another column next to it showing just the order_lookup_codeRenno– Renno2021-05-13 05:43:00 +00:00Commented May 13, 2021 at 5:43
-
Do you have an upper limit of the number of elements? You can't have dynamic columns in a SQL query. So you need to write a query with a pre-defined number of columnsuser330315– user3303152021-05-13 06:26:38 +00:00Commented May 13, 2021 at 6:26
Add a comment
|
2 Answers
If you want multiple columns, you need one expression per column, e.g.
select vendor_provided_data #>> '{vendor_data, 0, order_lookup_code}' as code_1,
vendor_provided_data #>> '{vendor_data, 1, order_lookup_code}' as code_2,
vendor_provided_data #>> '{vendor_data, 2, order_lookup_code}' as code_3,
...
from the_table
The #>> operator extracts a "nested" element from a JSON value, along a "path" where each element of the array specifies a step in the path. The same could be achieved using multiple -> operators. {vendor_data, 0, 'order_lookup_code'} is equivalent to -> 'vendor_data' -> 0 -> 'order_lookup_code'
If multiple rows are OK, you can use jsonb_array_elements()
select t.id, --<< whatever columns you want from the table
vd.item ->> 'order_lookup_code' as lookup_code
from the_table t
cross join jsonb_array_elements(t.vendor_provided_data -> 'vendor_data') as vd(item)
If you are on Postgres 12 or later, you could extract all codes as a single JSON array:
select jsonb_path_query_array(vendor_provided_data, '$.vendor_data[*].order_lookup_code') as all_codes
from the_table
I assume the vendor_provided_data is your jsonb column . This query would return you all records of table shipment with selection of order_lookup_code .
select jsonb_array_elements(vendor_provided_data->'vendor_data')->>'order_lookup_code' order_lookup_code
from shipment
where 1 = 1
