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?


Show source
| ruby   | json   | postgresql   | ruby-on-rails   | sql   2016-12-19 16:12 1 Answers

Answers ( 1 )

  1. 2016-12-19 18:12

    Use json_array_elements() and json_each_text() in lateral joins to get most inner json objects. Use distinct to get unique values:

    with shipment as (
    select
    '[
        {
            "reference_field_name": "freight_number", 
            "reference_field_value": "0098"
        }, 
        {
            "reference_field_name": "bill_of_lading", 
            "reference_field_value": "1190"
        }
    ]'::json as reference_number
    )
    
    select distinct value
    from shipment,
    lateral json_array_elements(reference_number) elem(elem),
    lateral json_each_text(elem) 
    where key = 'reference_field_name';
    
         value      
    ----------------
     bill_of_lading
     freight_number
    (2 rows)    
    
◀ Go back