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
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'
);