检查PostgreSQL JSONB列中的密钥存在

Question

我在Postgres DB中有一个JSONB列。我需要查询在列中存储的JSON中存在特定的密钥" SSN"。该键不是顶级键，而是嵌套。我写了一个类似于下面的查询，但似乎没有检索适当的列 -

select * from application where content ? 'ssn';

当我使用顶级密钥运行上述查询时，它可以正常工作。

{  
   "name":"bob",
   "business":{  
      "ssn":"XXXXXXXX",
    }
}

Answer 1

如果您知道SSN密钥的所有可能位置，则可以使用查询：

select *
from application
where content #> '{business,ssn}' is not null
  or content #> '{whatever,deep,ssn}' is not null;

Answer 2

使用提取元素 - att-at-at-at path操作员#>>，并检查给定路径的值是否为null。

示例：

WITH test(col) AS (
SELECT UNNEST(
  ARRAY['{"name": "bob", "business": {"ssn": "abcd"}}'::jsonb, 
        '{"name": "jen", "business": {"ssn": "1234"}}',   
        '{"name": "kay", "nonprofit": {"no-ssn": "no-ssn"}}'
        '{"name": "todd", "business": {"no-ssn": "no-ssn"}}']
))
SELECT * FROM test
WHERE col#>>'{business, ssn}' IS NOT NULL

Answer 3

you

1. 要么必须编写递归查询才能解析JSON的结构并检查每个分支中的密钥，
2. 或简单地将模式与JSONB的文本表示匹配

smth喜欢：

select * from application 
where content::text like '%"ssn":%';