javasqljsonpostgresqlpg

Is there a way to find all objects in an array whose fields all objects have the same value postgresql


Let's see a few objects

{
"name: "Test_1",
"attributes": [
    {
        "attribute_name" : "Some_name"
    },
    {
        "attribute_name" : "Some_name_2"
    }
],
"phoneNumber" : "N"
},

{
"name: "Test_2",
"attributes": [
    {
        "attribute_name" : "Some_name"
    },
    {
        "attribute_name" : "Some_name",
        "attribute_phoneNumber": "N1"
    }
],
"phoneNumber" : "N2"
}

So, I wanna get objects whose attributes array elements (all of them) have attribute_name "Some_name"

attributes.attribute_name = Some_name

and as result I wanna get object with Test_2 name because all of attributes elements have this name.

This array 'attributes' saved as jsob in PG, so I tried to use some functions like json_array_elements(...) but it didn't helped https://www.postgresql.org/docs/9.5/functions-json.html


Solution

  • Assuming that these are JSONB fields from 2 records of your table as illustrated in the the_table CTE, then select these records for which does not exist an attributes array element whose attribute_name value is not equal to 'Some_name'. coalesce is there for missing attribute_name elements.

    with the_table(name, attributes, phonenumber) as 
    (
     values
     ('Test_1', '[{"attribute_name" : "Some_name"}, {"attribute_name" : "Some_name_2"}]'::jsonb, 'N'),
     ('Test_2', '[{"attribute_name" : "Some_name"}, {"attribute_name" : "Some_name", "attribute_phoneNumber": "N1"}]', 'N2')
    ) -- sample data table
    
    select * from the_table where not exists 
    (
     select from jsonb_array_elements(attributes) j 
     where coalesce(j->>'attribute_name', '') <> 'Some_name'
    );