Search code examples
arrayspostgresqljsonb

How to get and compare the elements of the jsonb array in Postgres?

Postgres 9.6.1


    CREATE TABLE "public"."test" (
    "id" int4 NOT NULL,
    "packet" jsonb,
    )
    WITH (OIDS=FALSE)
    ;

Jsonb


    {"1": {"end": 14876555, "quantity":10}, "2": {"end": 14876555, "quantity":10} }

or 


    [{"op": 1, "end": 14876555, "quantity": 10}, {"op": 2, "end": 14876555, "quantity": 20}]

All attempts to retrieve an array results in an error: cannot extract elements from an object

It is necessary to compare all the elements "end" < 1490000 and find the id.

"op": 1 or "1": variable value and the full path is not suitable for solutions

Solution

  • If you have no the agreed JSON structure the best solution IMO is something like

    select *
from
  public.test,
  regexp_matches(packet::text, '"end":\s*(\d+)', 'g') as e(x)
where
  x[1]::numeric < 1490000;