postgresql: Filter in JSON array

Question

Let's say we have a table items which has columns name and attributes:

CREATE TABLE students (
  name VARCHAR(100),
  attributes JSON
)

where attributes is an array of (always equally-structured) JSON documents such as

[{"name":"Attribute 1","value":"Value 1"},{"name":"Attribute 2","value":"Value 2"}]

I now want to find all students where any attribute value matches something (such as Foo%). Here's a playground example.

I realize that this isn't exactly the most straight-forward design, but for now it's what I have to work with, though performance of such a search being categorically terribly inefficient would of course be a valid concern.

Answer 1

You may use json_array_elementsto access the elements and then use ->> json operator to search using some value.

select s.*,j from 
  students  s 
   cross join lateral json_array_elements ( attributes ) as j
WHERE j->>'value' like 'Foo%'

Demo

Edit

The problem here now is that the cross join will "duplicate" rows. Is there a better way to avoid this

use WITH ORDINALITY to generated id per element and then use DISTINCT ON to get the first / last match per student.

select DISTINCT ON (name) s.*,j.attr from 
students  s 
cross join lateral json_array_elements ( attributes ) WITH ORDINALITY as j(attr,id)
WHERE j.attr->>'value' like 'Value%'
ORDER BY name,j.id

Demo2