CODEWITHSUNDEEP
postgresqljsonb
Renno
Renno

Reputation: 13

How to extract from jsonb array postgres?

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"}]}

Upvotes: 0

Views: 3833

Answers (2)

Ramakrishna Hosmani
Ramakrishna Hosmani

Reputation: 19

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

Upvotes: 0

user330315
user330315

Reputation:

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

Upvotes: 2

Related Questions