查找PostgreSQL JSONB列中嵌套键的值

Question

给定以下json记录:

记录1:

{"Name":{"type":"String", "value":"Person1"}, "Spouse":{"Type":"foreign-key", "value":"123"}}

记录2:

{"Name":{"type":"String", "value":"Person2"}, "Sibling":{"Type":"foreign-key", "value":"345"}}

什么是json查询被用来获取所有值的键"值"，其中"类型"是"外键"，无论父键("配偶"/"兄弟姐妹")?

给定这两条记录，结果应该是"123"，"345"

Answer 1

找到解决方案，使用jsonb_each函数和'contains'操作符(@>):

select
    jt.id as jt_id,
    (kv.value -> 'value')::text::int as entity_id
from 
    json_table jt, 
    jsonb_each(jt.values) kv
where
    kv.value @> '{"Type":"foreign-key"}'