I have a table in PostgreSQL

CREATE TABLE IF NOT EXISTS account_details
(
    account_id integer,
    condition json
);

And the data's present inside this table are

I need to fetch the details of all account having rootcompanyid in (5). IF part of any **rootcompanyid's ** are present in any of the account details should display the result. So output should contain account_id --> 1 and 3 rows

The below query is fetching only the last row (account_id = 3) not the first row

SELECT *
  FROM account_details
 WHERE ((condition->0->>'conditions')::json->>'rootcompanyid')::json->>'$in' = '[5]';

Expected Output : IF part of any **rootcompanyid's ** are present in any of the account details should display the result.

Please provide me any solutions as I'm new to handle Json conditions

CodePudding user response：

That's easily done with the JSON containment operator:

WHERE condition @> '{ "conditions": { "rootcompanyid": { "$in": [5] } } }'

CodePudding user response：

You can use the ->> operator to extract the value of a nested key in a JSON field in PostgreSQL. The syntax for filtering based on the value of a nested key is as follows:

SELECT * FROM table_name WHERE json_field->>'nested_key' = 'value';

Replace table_name with the name of your table, json_field with the name of the JSON field, nested_key with the name of the nested key, and value with the desired value.