Postgres: Querying multiple jsonb fields

Question

Having a table with a column containing jsonb data, I want to query those data with multiple fields. The jsonb structure looks like this:

{
  "https://example.tld/": 
  [
    {
      "content-length": 312,
      "path": "page.log",
      "redirect": null,
      "status": 200
    },
    {
      "content-length": 312,
      "path": "pages/",
      "redirect": null,
      "status": 200
    },
    ...
  ]
}

I want to query all log files with status 200, so I did

SELECT json_data -> 'path' AS path
FROM table
WHERE json_data->'status' = 200 AND json_data->'path' ILIKE '%.log%'

However, it does not work. The closest variant I get to work is if I cast the json content into text and search for log, but it returns the whole content.

SELECT *
FROM table
WHERE json_data::text ILIKE '%.log%'

Thanks for your help!

Answer 1

If I understand correctly, you want the paths from the underlying array in JSON. If so:

select rs.path
from t cross join
     jsonb_to_recordset(t.json -> 'https://example.tld/') rs(status int, path text)
 where rs.path ilike '%.log%' and rs.status = 200

Here is a db<>fiddle.