Postgresql JSON列检查键是否存在

Question

我已经写了这样的查询来检查JSON列是否有键：

SELECT *
FROM "details" 
where ("data"->'country'->'state'->>'city') is not null; 

我们如何编写查询语句，以便在“data”包含“city”的情况下选择行？

数据的JSON结构不一致。

- Aniket Kulkarni

Answer 1

你可以通过在 文档 中提到的 ? 来检查 data 的 顶级 键。

例如：

SELECT * FROM details
WHERE data ? 'city';

检查json列中所有嵌套对象中的每个键需要使用递归CTE。

select * from details
where 'city' in (
    WITH RECURSIVE t(k,j) as (
        select jsonb_object_keys(details.data), details.data
    UNION ALL
        select jsonb_object_keys(t.j->t.k), t.j->t.k
        FROM t WHERE jsonb_typeof(t.j->t.k) = 'object'
    )
    select k from t
);

当然，这并不是非常高效的。

- Sevanteri

---

这适用于JSONB列。问题涉及JSON列，所以您需要进行类型转换： SELECT * FROM details WHERE data::JSONB ? 'city'; - Bernhardt Scherer

Answer 2

您可以使用？：

SELECT *
FROM "details" 
WHERE data->'country'->'state' ? 'city';

- TitanFighter

Answer 3

您可以将JSON转换为文本，然后使用LIKE搜索键。

SELECT *
FROM "details"
WHERE "data"::text LIKE '%"city":%'

- Philip M

---

有些JSON值可能包含“city”文本。你如何区分这是键名称还是普通文本？ - Eugen Konkov

这对我的情况有效，其中值是像这样的列表["abc", "edf"]。 - Chau Loi

这个查询与被接受的答案相比，性能会有所下降。 - Priyam