CODEWITHSUNDEEP
sqlpostgresql
Ingo Bürk
Ingo Bürk

Reputation: 20043

postgresql: Filter in JSON array

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.

Upvotes: 18

Views: 52533

Answers (3)

Sayed Ajmal
Sayed Ajmal

Reputation: 19

I searched a lot for an easier solution for a clean where clauses in Laravel and and couldn't find it, so I came up with bellow idea.

SELECT * FROM students where lower(attributes::text) ilike '%"<key>"%"<value>"%'

If your parse json properly key and values are all stored in (") double quotes.

Notice I added (") double quotes to kay and value so it matches the exact key and value.

Upvotes: 1

Chhunly Lim
Chhunly Lim

Reputation: 170

You can use EXISTS function to filter sub json data

SELECT s.* FROM
students s 
WHERE EXISTS(
SELECT 1 FROM json_array_elements ( attributes ) as att WHERE att ->> 'value' ILIKE 'Foo%')

Upvotes: 1

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

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

Upvotes: 30

Related Questions