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

Do you use a programming language? Something like c# or java. You can parse there. Or you are limited only on postgres? — Adrian Stanculescu
– Adrian Stanculescu, Commented Nov 22, 2019 at 20:46
Can you be a bit more clear on the result you are trying to get? Showing the result explicitly would be ideal. — Jeremy
– Jeremy, Commented Nov 22, 2019 at 20:59
I'm just running straight queries on Postgres (using DBeaver) for now to write use cases for the data, so no programming language. And let me update with a desired result of what I'm trying to get. — PainIsAMaster
– PainIsAMaster, Commented Nov 22, 2019 at 21:10
Is there always a single json object in the array, or there could be several? — GMB
– GMB, Commented Nov 22, 2019 at 21:41
@GMB There is always only 1 JSON object (with key/value pairs separated by commas) in that array, which is inside another JSON object in that database (PostgeSQL) column. I'm an intern and I didn't design it this way :D There will always only be the 8 keys , i.e. dcn, dcnName, ucid, ucidName, dealerCode, and dealerName , with their respective values inside that object. That will always be consistent. — PainIsAMaster
– PainIsAMaster, Commented Nov 25, 2019 at 20:00

user330315 · Accepted Answer · 2019-11-22 21:55:15Z

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

answered Nov 22, 2019 at 21:55

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

PainIsAMaster Over a year ago

Thanks for the input, I will check that out right now. And sorry if my explanation isn't the best. I'm an intern and was giving a project to query this database the company has and I don't really understand the data or how its structured but they wanted me to find some ground truths for integration testing. I never had to query json nested inside of columns in the DB before.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related