PostgreSQL query column that has JSON object with array of another JSON object nested inside

Question

The JSON column data is formatted as such:

{
    "associations": [
        {
            "dcn": "FI692HI", 
            "ucid": "1038753892", 
            "dcnName": "USED PARTS 4 SALE", 
            "ucidName": "A UCID NAME", 
            "dealerCode": "A187", 
            "dealerName": "SOME DEALER HERE LTD."
        }
    ]
}

or like this in the bigger picture

party_no   mdl   mkr_cd   assoc_strct 
-------------------------------------
666        DOG      2     JSON object from above
267        DOG      1     JSON object from above
185        CAT      1     JSON object from above

I need to be able to query the keys in that JSON object that is inside the array, that is, I need to do queries for dcn, dcnName, ucid, ucidName, dealerCode, and dealerName values, like how you would do in a hash map in java or dictionary in python

SELECT
assoc_strct -> 'associations'  AS json_array
FROM
assets.asset_latest al

So basically, say I wanted to query to see what the most frequency appearing value was for the "dcn" key and get its corresponding party_no lets say. so lets say I had a "dcn" key with a value of "BLUE42" appearing 1 million times, my results should be like:

party_no   JSON val    count 
--------------------------------
666        BLUE42    1,000,000    

Again, I just need a method to query the key/value pairs inside this JSON object, which holds an array, and then another JSON object that contains key/value pairs delimited by a comma (oh that's a mouthful). Not entirely sure who created the database with the JSON column that way (its my work), because I figured a { "outer_json_object" : [{"key" : "pair"}, {"key2": "pair2"}]} would be easier to access but maybe I'm wrong

Answer 1

I am not entirely sure I understand your question completely.

You will need to unnest the array for each party_no, then retrieve all the key/value pairs from the array elements, which essentially returns one row for each key/value pair times the number of elements in the array - for each party_no.

The unnesting is done using jsonb_array_elements() and extracting the key/value pairs can be done using jsonb_each().

The result can then be grouped and sorted descending and the first row is the one with the highest count:

select party_no, 
       t.val as "JSON Value", 
       count(*)
from data
  cross join jsonb_array_elements(assoc_strct -> 'associations') as a(e)
  cross join jsonb_each_text(a.e) as t(ky,val) 
where t.ky = 'dcn'  
group by party_no, t.val
order by count(*) desc
limit 1  

Online example: https://rextester.com/LYTTY41242