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!

Which Postgres version are you using?

user330315
– user330315

2020-08-14 11:43:38 +00:00
Commented Aug 14, 2020 at 11:43 — user330315
– user330315, Commented Aug 14, 2020 at 11:43
(PostgreSQL) 10.12

nullpointr
– nullpointr

2020-08-14 11:48:30 +00:00
Commented Aug 14, 2020 at 11:48 — nullpointr
– nullpointr, Commented Aug 14, 2020 at 11:48

Gordon Linoff · Accepted Answer · 2020-08-14 11:46:50Z

3

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.

answered Aug 14, 2020 at 11:46

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

3 Comments

nullpointr Over a year ago

Thanks a lot! How can I make the query independent from a specific domain? Is there something like t.json->[0] possible? I mean to query the first array entry without specifying domain name like example.tld.

nullpointr Over a year ago

@a_horse_with_no_name i get "ERROR: cannot call jsonb_to_recordset on a non-array"

Gordon Linoff Over a year ago

@nullpointr . . . (1) I would suggest that you ask a new question. (2) The simplest method is to include the domain in the array, rather than having it be a key.

Mike Organek · Accepted Answer · 2020-08-14 11:59:26Z

Assuming the top-level key name (https://example.tld/ in this case) changes from row to row in your table, you will not be able to use a fixed path and will have to expand the object with jsonb_each() before expanding the array elements with jsonb_array_elements().

with indat as ( select 1 as id, '{
  "https://example.tld/":
  [
    {
      "content-length": 312,
      "path": "page.log",
      "redirect": null,
      "status": 200
    },
    {
      "content-length": 312,
      "path": "pages/",
      "redirect": null,
      "status": 200
    }
  ]
}'::jsonb as json_data
)
select i.id, j.key, e.element
  from indat i
 cross join lateral jsonb_each(json_data) as j(key, value)
 cross join lateral jsonb_array_elements(j.value) as e (element)
 where e->>'path' ilike '%.log%'
   and e->>'status' = '200';

 id |         key          |                                   element
----+----------------------+------------------------------------------------------------------------------
  1 | https://example.tld/ | {"path": "page.log", "status": 200, "redirect": null, "content-length": 312}
(1 row)

Collectives™ on Stack Overflow

Postgres: Querying multiple jsonb fields

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related