Selecting unique values of keys inside postgresql json field

Question

I have the following model:

Shipment:
  reference_numbers: [] :json field

reference fields general structure is as follows:

[{'reference_field_name': 'freight_number', 'reference_field_value': '0098'}, {'reference_field_name': 'bill_of_lading', 'reference_field_value': '1190' }]

Here the field name can vary from delivery to delivery. It can be anything not just bill_of_lading and freight_number.

What is the best way to find all unique values of 'reference_field_name' across reference_numbers across Shipments?

Answer 1

I know, it's a bit late for answer but for thouse of you who have similar task, there is some cool postgresql features :)

First, let's unnest reference_numbers into table:

select jsonb_array_elements("reference_numbers") as fields from shipment

This will return a rowset with one column, each row will have one item from all reference_numbers arrays. So, if you have two shipment rows and each one have two items in reference_numbers field, than this query will return 4 items.

Second, let's select distinct fields:

with fields_rowset as (
    select 
        jsonb_array_elements("reference_numbers") as fields 
    from shipment
)
select DISTINCT fields->'reference_field_name' from fields_rowset

Note, i've used jsonb_* function but if you have json field you have to use json_* alternative.

Answer 2

SELECT DISTINCT(jsonb_object_keys(outputs)) FROM shipment;