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

Question

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

Answers 1
Added at 2016-11-18 23:11
Tags
Question

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

Answers to

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

nr: #1 dodano: 2016-11-19 00:11

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;
Source Show
◀ Wstecz