1

I'm trying to query a jsonb column in Postgres. I've created it from a nested java map, it isn't in array format, and can't work out if that is the thing that is causing me to have issues with my query.

I want to query the jsonb column for items containing an object with '"type": "Unknown" and also have a time that is greater than 100.

id|uid                                 |process_stat_json                                                                                                                                                                                                                                                           |
---------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 1|9cf237e8-0c73-4c4d-a60c-30b244789e67|{"Type A": {"time": 500, "complete": 100.0, "type": "Unknown"}, "Type B": {"time": 35, "complete": 100.0, "type": "Parallel"}, "Type C": {"time": 50, "complete": 100.0, "type": "Serial"}}
 2|07ac957f-c9e4-460e-b75f-79c0d9c550d4|{"Type A": {"time": 55, "complete": 100.0, "type": "Parallel"}, "Type C": {"time": 20, "complete": 100.0, "type": "Serial"}}
 3|8e8ef827-fff1-4ea1-a990-206b557ef27a|{"Type C": {"time": 100, "complete": 100.0, "type": "Parallel"}, "Type A": {"time": 55, "complete": 100.0, "type": "Unknown"}, "Type D": {"time": 70, "complete": 100.0, "type": "Serial"}}
 4|5279e6ae-f2e8-4fae-8d0b-9297b5e2936a|{"Type A": {"time": 200, "complete": 100.0, "type": "Serial"}, "Type D": {"time": 20, "complete": 100.0, "type": "Unknown"}, "Type C": {"time": 80, "complete": 100.0, "type": "Serial"}}

So for example the query would return id:1

I've started creating a query to search for items with the type "Unknown"

select p.*
from
  process p
where
  p.process_stat_json  @> '{"type":"Unknown"}';

But this isn't even returning any items at all, let alone allowing me to query them, so unsure how to proceed from here.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Rafe
  • 512
  • 1
  • 4
  • 15

1 Answers1

1

Your first request can be solved elegantly with the JSON path operator @> - in Postgres 12 or later:

SELECT p.*
FROM   process p
WHERE  p.process_stat_json @? '$.* ? (@.type == "Unknown") ? (@.time >= 100)';

fiddle

Related:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    Your second request is not entirely clear. I suggest we focus on your first request here, and you start a new question with all relevant details for the second. The guideline here is *one* question per question anyway. – Erwin Brandstetter Jul 13 '23 at 09:58
  • Thanks for that, am getting my head around the operator now! – Rafe Jul 14 '23 at 01:05