Jesse Jesse - 1 year ago 187
JSON Question

Postgresql: Find row via text in json array of objects

I am trying to find rows in my Postgresql Database where a json column contains a given text.

row schema:

id | name | subitems
1 | "item 1" | [{name: 'Subitem A'}, {name: 'Subitem B'}]
2 | "item 2" | [{name: 'Subitem C'}, {name: 'Subitem D'}]

My wanted result for query 'Subitem B'

id | name | subitems
1 | "item 1" | [{name: 'Subitem A'}, {name: 'Subitem B'}]

I can search for the first subitem like this:

WHERE lower(subitems->0->>\'name\') LIKE '%subitem a%'

But obviously I can't find any other subitem but the first one this way.

I can get all the names of my subitems:

SELECT lower(json_array_elements(subitems)->>'name') FROM ...

But it gives me 2 rows containing the names:

"subitem a"
"subitem b"

What I actually need is 1 row containing the item.

Can anyone tell me how to do that?

Answer Source

You're almost there. Your query:

SELECT lower(json_array_elements(subitems)->>'name') FROM foo;

That gets you what you want to filter against. If you plop that into a subquery, you get the results you're looking for:

    FROM foo f1
    WHERE 'subitem a' IN
      (SELECT lower(json_array_elements(subitems)->>'name')
        FROM foo f2 WHERE =
 id |  name  |                    subitems                    
  1 | item 1 | [{"name": "Subitem A"}, {"name": "Subitem B"}]
(1 row)

Edited to add

Okay, to support LIKE-style matching, you'll have to go a bit deeper, putting a subquery into your subquery. Since that's a bit hard to read, I'm switching to using common table expressions.

WITH all_subitems AS (
  SELECT id, json_array_elements(subitems)->>'name' AS subitem
    FROM foo),
matching_items AS (
    FROM all_subitems
      lower(subitem) LIKE '%subitem a%')
  FROM foo
    id IN (SELECT id from matching_items);

That should get you what you need. Note that I moved the call to lower up a level, so it's alongside the LIKE. That means the filtering condition is in one spot, so you can switch to a regular expression match, or whatever, more easily.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download