CODEWITHSUNDEEP
sqljsonpostgresqljsonb
Bravo
Bravo

Reputation: 9027

how to query nested array JSON in postgres?

I have following structure of JSON document storing in one of my POSTGRES table

link to the sample JSON is here

here in that JSON , i have below structure inside nested array ,

"product_order_reference": {
                            "purchase_order_number": "0007-8653547-0590"
                        }

i am trying to retrieve JSON , which have the supplied purchase order number , i tried below queries , even though their are JSON rows for that purchase order numbers , query returning nothing

queries i tried :

SELECT * from edi_records , jsonb_array_elements(valid_record :: jsonb ->'loop_id_hls') hls,jsonb_array_elements(hls->'loop_id_hlo') hlo where hlo->'product_order_reference' ->> 'purchase_order_number' = '0007-8653547-0590';


SELECT * from edi_records , jsonb_array_elements(valid_record :: jsonb ->'loop_id_hls') hls,jsonb_array_elements(hls->'loop_id_hlo') hlo where hlo ->> 'purchase_order_number' = '0007-8653547-0590';


SELECT * from edi_records , jsonb_array_elements(valid_record :: jsonb ->'advance_shipment_notice'::text->'loop_id_hls') hls,jsonb_array_elements(hls->'loop_id_hlo') hlo where hlo ->> 'purchase_order_number' = '0007-8653547-0590';



SELECT track_num from edi_records , jsonb_array_elements(valid_record :: jsonb ->'advance_shipment_notice'->'loop_id_hls') hls,jsonb_array_elements(hls->'loop_id_hlo') hlo where hlo -> 'product_order_reference'->> 'purchase_order_number' ::text = '0007-8653547-0590';

can any one please help me how to solve this , i am stuck here with this .

Upvotes: 0

Views: 403

Answers (1)

Dan
Dan

Reputation: 2705

I copy and pasted your JSON object. It's a bit large but I was able to get the order number. The main hassle is all the nested arrays.

Downside is that I am digging into the json object manually. If the structure change or if the keys contain duplicate objects that requires a bit of searching, then the results would be wrong. I am sure this can be improved.

SELECT
  your_json -> 'advance_shipment_notice'
    -> 'loop_id_hls'
    -> 0 -- {loop_id_hls}
    -> 'loop_id_hlo'
    -> 0 -- {loop_id_hlo}
    -> 'product_order_reference'
    -> 'purchase_order_number' AS purchase_order_number
FROM your_json;

Upvotes: 1

Related Questions