Postgresql – How to query JSONB field type

postgresql-13

I have a jsonb field named 'queues' it contains the following:

{
  "call_queue_pid": [
    1,
    2,
    3
  ],
  "omni_queue_pid": [
    4,
    5,
    6
  ]
}

I try the below query to search omni_queue_pid = 5

Select * 
from ws a,
    jsonb_array_elements(a.queues::jsonb) j 
Where cast(j->> 'omni_queue_pid' as integer) = 5

but it returns SQL Error [22023]: ERROR: cannot extract elements from an object

what I missed?

need help

thanks
Don

Best Answer

You need to unnest the array:

Select * 
from ws a
  cross join jsonb_array_elements_text(a.queues -> 'omni_queue_pid') as o(id)
Where o.id::int = 5

It's unclear what result you exactly want, but an EXISTS condition might be more efficient:

select * 
from ws a
where exists (select *
              from jsonb_array_elements_text(a.queues -> 'omni_queue_pid') as o(id)
              Where o.id::int = 5)

Or using a jsonpath operator:

select * 
from ws
where queues @? '$.omni_queue_pid[*] == 5'

Related Solutions

Postgresql – How to conditionally create JSON array in postgres with group by clause

Use conditional aggregation to get the sums depending on is_tag_stock and jsonb_build_object() (or json_build_object()) to create the encapsulating JSON.

SELECT cat_id,
       cat_name,
       jsonb_build_object('piece',
                          sum(CASE
                                WHEN is_tag_stock THEN
                                  piece
                              ELSE
                                0
                              END)) tagged,
       jsonb_build_object('piece',
                          sum(CASE
                                WHEN NOT is_tag_stock THEN
                                  piece
                                ELSE
                                  0
                             END)) untagged
       FROM elbat
       GROUP BY cat_id,
                cat_name;

db<>fiddle

Postgresql – How to use NOTIFY with postgres to send previous record and new record

You can call it from a trigger and pass both OLD and NEW in the notify payload. But by default the payload accepts only a maximum of 8000 bytes total, so if your JSON BLOBs are bigger than 4000 bytes each you will need to insert at least the old value in a table and pass the key in the payload.

If you are attempting to build logical replication, you might be better off using those facilities in newer versions of Postgres. You should be planning to upgrade soon as you are now 4 major versions behind current.

Best Answer

Related Solutions

Postgresql – How to conditionally create JSON array in postgres with group by clause

Postgresql – How to use NOTIFY with postgres to send previous record and new record

Related Question