20

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.

Improve this question
2
  • 2
    json[] never makes sense. It's better to store a "real" JSON array inside a json column. Commented Dec 3, 2018 at 10:09
  • 1
    @a_horse_with_no_name That would also be fine. I'll change the questiion to be like that instead. Commented Dec 3, 2018 at 10:10

3 Answers 3

Reset to default
31

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

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

8 Comments

Thanks! The cross join lateral is what I was missing (and will have to look up).
The problem here now is that the cross join will "duplicate" rows. Is there a better way to avoid this other than selecting a distinct list of the IDs and putting it into an outer query where I select the actual rows with those IDs?
@IngoBürk : Not sure what duplicates you are talking about, there's only one row per one json element in the array. You could use a DISTINCT ON based on some order by to limit them to highest /lowest per match. You should probably ask another question with the details.
Yes, it returns one row per one element in the array, but that's not the expected output; the expected output is the students where any element matches (i.e., each student only once). I'm not interested in the matching JSON element, just that at least one matched. I do somewhat consider this part of this question as that is how I phrased the question.
@IngoBürk : Well, then you could use GROUP BY /DISTINCT ON to get aggregate/top element based on an order. See my other answers. here or this one
|
2

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%')
Improve this answer

2 Comments

in this case, do you don't filter the content of json array "attributes"
Oh, ok I got it. I was thinking that filtering json array wasn't in need.
1

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.