Postgresql: Find row via text in json array of objects

Question

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:

lower
----------------------------------------------------------------
"subitem a"
"subitem b"

What I actually need is 1 row containing the item.

Can anyone tell me how to do that?

Answer 1

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:

# SELECT *
    FROM foo f1
    WHERE 'subitem a' IN
      (SELECT lower(json_array_elements(subitems)->>'name')
        FROM foo f2 WHERE f1.id = f2.id
      );
 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 (
  SELECT id
    FROM all_subitems
    WHERE
      lower(subitem) LIKE '%subitem a%')
SELECT *
  FROM foo
  WHERE
    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.