0

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"

Improve this question
3
  • Please also post your query. Commented Mar 30, 2020 at 8:15
  • 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 Commented Mar 30, 2020 at 8:24
  • Unrelated to your question, but using a JSONB column as the primary key seems rather strange. Commented Mar 30, 2020 at 8:35

1 Answer 1

Reset to default
3

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

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

Online example

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.