How to write Postgres JSONB Where clausule

Question

I have the following Table:

CREATE TABLE mbiz.dictionary_groups (
    slgr_id jsonb NOT NULL,
    stored jsonb NOT NULL,
    modified_date timestamp NOT NULL DEFAULT now(),
    CONSTRAINT dictionary_groups_pkey PRIMARY KEY (slgr_id)
);

and keeps json object in column called 'stored', example:

{
    "Position": 
        {"RotationId": 0, "SubGroupId": 0, "DiscoutGroupId": 99, "PriceIntervalId": 0},
    "DefaultValue": 0.0, 
    "PositionValues": 
    [
        {"Value": 26.0, "ProfileId": 1}, 
        {"Value": 18.0, "ProfileId": 2}, 
        {"Value": 33.0, "ProfileId": 12}
    ]
}

I'm trying to find all records where any record in 'PositionValues' will have 'ProfileId' equal to 2.

It's a Postgres 9.5, I found some tips where users suggest to use ? or @> but when I tried it, I received error message:

"SQL Error [42883]: ERROR: operator does not exist: @> unknown
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Position: 85"

This query is not working: SELECT * FROM dictionary_groups sgr WHERE sgr.stored -> 'PositionValues' -> 'ProfileId' = '2' ; Probably there is a need to use ANY or something similar — Lucas
Unrelated to your question, but using a JSONB column as the primary key seems rather strange. — a_horse_with_no_name

a_horse_with_no_name a_horse_with_no_name · Accepted Answer · 2020-03-30T08:29:17

You need to provide an array on the right hand side:

select *
from dictionary_groups
where "stored" -> 'PositionValues' @> '[{"ProfileId": 2}]';

Online example

How to write Postgres JSONB Where clausule

1 Answers