CODEWITHSUNDEEP
postgresqljsonbpostgresql-9.5
Lucas
Lucas

Reputation: 67

How to write Postgres JSONB Where clausule

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"

Upvotes: 0

Views: 45

Answers (1)

user330315
user330315

Reputation:

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

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

Online example

Upvotes: 3

Related Questions