sql - 从 json_agg() 对象数组中选择 id

Question

我有这个变量，

DECLARE
js jsonb := (SELECT json_agg(t) FROM (Select parent_id, id from my_schema.organization org where org.parent_id is nul) t);
// js contains [{parent_id: 'value', id: 'value'}]
// I want, orgsIds = [id from first obj, id from second obj, ...]

我想从对象数组中in查询 id 。js

SELECT * FROM my_schema.organization where id = ANY(orgIds)

标签： sqlpostgresql