我在PostgreSQL中有一张表
CREATE TABLE IF NOT EXISTS account_details
(
account_id integer,
condition json
);
这张表中的数据是
account_id | condition |
---|---|
1 | [{"action":"read","subject":"rootcompany","conditions":{"rootcompanyid":{"$in":[35,20,5,6]}}}] |
2 | [{"action":"read","subject":"rootcompany","conditions":{"rootcompanyid":{"$in":[1,4,2,3,6]}}}] |
3 | [{"action":"read","subject":"rootcompany","conditions":{"rootcompanyid":{"$in":[5]}}}] |
我需要获取所有帐户在(5)中有rootcompanyid的详细信息.如果任何帐户详细信息中存在任何**rootpananyid**的一部分,则应显示结果. 因此,输出应包含account_id --> 1 and 3行
下面的查询只读取最后一行(count_id=3),而不是第一行
SELECT *
FROM account_details
WHERE ((condition->0->>'conditions')::json->>'rootcompanyid')::json->>'$in' = '[5]';
Expected Output:如果任何帐户详细信息中存在任何**rootpananyid**的一部分,则应显示结果.
account_id | condition |
---|---|
1 | [{"action":"read","subject":"rootcompany","conditions":{"rootcompanyid":{"$in":[35,20,5,6]}}}] |
3 | [{"action":"read","subject":"rootcompany","conditions":{"rootcompanyid":{"$in":[5]}}}] |