up vote 0 down vote favorite

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 .

share|improve this question
up vote 1 down vote

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;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.