Postgres get all elements where value matches from jsonb array

Question

I think I am trying something simple, but after hours I just can't get it to work. I have a table which contains a tags column, which is a jsonb array and looks like this:

[
    {
        "name": "e-commerce",
        "weight": 400
    },
    {
        "name": "management",
        "weight": 200
    },
    {
        "name": "content management",
        "weight": 200
    }
]

I now want to write a query which returns the full object to me, when the name matches the search string. So far I came up with this:

SELECT * FROM data
WHERE tags is not null
  AND EXISTS(
        SELECT FROM data ad WHERE (ad.tags -> 'name' ->> 'education')::text
    );

But I get this error:

[42883] ERROR: operator does not exist: jsonb[] -> unknown Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

I am not sure where I should do a typecast and whether that is really the problem.

I already tried solutions from these threads, but to no avail :-(

Is that really the content of your JSON column? Those escaped double quotes look wrong, it seems the value has been stored as a single scalar values, rather than a proper JSON object. — user330315
– user330315, Commented Dec 20, 2020 at 16:06
I am not the most familiar with arrays and objects in postgres. My colleague created the table, but when I query it, I get back an array of objects. — Gh05d
– Gh05d, Commented Dec 20, 2020 at 16:16
I just checked the database definition and it is indeed a jsonb: tags jsonb[] default'{}'::jsonb[] — Gh05d
– Gh05d, Commented Dec 20, 2020 at 16:20
jsonb[] almost never makes sense. A "plain" jsonb that includes a real JSON array is a much better choice — user330315
– user330315, Commented Dec 20, 2020 at 16:34

GMB · Accepted Answer · 2020-12-20 17:13:13Z

10

If you want each matching object on a separate row, you can use jsonb_array_elements() to unnest the array of objects, then filter:

select o.obj
from data d
cross join lateral jsonb_array_elements(d.tags) o(obj)
where o.obj ->> 'name' = 'education'

That works in you have JSONB array (so the datatype of data is jsonb).

If, on the other hand, you have an array of json objects (so: jsonb[]), you can unnest instead:

select o.obj
from data d
cross join lateral unnest(d.tags) o(obj)
where o.obj ->> 'name' = 'education'

Note that this generates two rows when two objects match in the same array. If you want just one row, you can use exists instead:

select o.obj
from data d
where exists (
    select 1 from unnest(d.tags) o(obj) where o.obj ->> 'name' = 'education'
)

answered Dec 20, 2020 at 17:13

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Comments

Nikhil Patil · Accepted Answer · 2020-12-20 16:44:03Z

2

You need to query the objects within the json array. Create those objects using jsonb_array_elements and then query the json like -

SELECT d.* FROM data d, jsonb_array_elements(d.tags) tag
WHERE tag is not null and  WHERE (tag -> 'name') ? 'education'

Also, a note, in your original query

This -

WHERE (ad.tags -> 'name' ->> 'education')::text

Should be -

WHERE (ad.tags -> 'name') ? 'education'

Or

WHERE (ad.tags ->> 'name')::text = 'education'

EDIT 1:

Since your data type is not jsonb but jsonb[], you need to unnest it to jsonb using unnest -

SELECT * FROM data d, jsonb_array_elements(unnest(d.tags)) tagObj
WHERE tagObj is not null and  WHERE (tag -> 'name') ? 'education'

Or

select * from (
SELECT * FROM data d, jsonb_array_elements(unnest(d.tags)) tagobj
) a WHERE tag is not null and  WHERE (tag -> 'name') ? 'education'

First one may give error, due to tagObj not being available at that context

edited Dec 20, 2020 at 16:44

answered Dec 20, 2020 at 16:14

Nikhil Patil

2,5331 gold badge9 silver badges20 bronze badges

2 Comments

Gh05d Over a year ago

The second WHERE in your answer should be removed. Unfortunately, I get this error: [42883] ERROR: function jsonb_array_elements(jsonb[]) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 32

Nikhil Patil Over a year ago

@Gh05d, Yes, its because your data type is jsonb[] instead of jsonb, you need to unnest it into jsonb. I have updated the answer.

Collectives™ on Stack Overflow

Postgres get all elements where value matches from jsonb array

2 Answers 2

Comments

EDIT 1:

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

EDIT 1:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related