PostgreSQL中如何用JSONPath风格查询jsonb对象数组元素？

先看你给出的JSONB结构：

"data": { 
   "text1": [
      {
        "lang": "de",
        "property1": "abc",
        "property2": "def"
      },
      {
        "lang": "en",
        "property1": "ghi",
        "property2": "jkl"
      }
   ],
   "text2": [
      {
        "lang": "de",
        "property1": "mno",
        "property2": "pqr"
      },
      {
        "lang": "en",
        "property1": "stu",
        "property2": "vwx"
      }
   ]
}

关于JSONPath风格的WHERE子句查询 #

PostgreSQL确实支持JSONPath语法，但你写的->[?(@.lang=="de")]这种链式写法是错误的，得用PostgreSQL提供的JSONPath操作符或函数来实现：

单条件查询示例 #

要实现你想要的“查询text1数组中存在lang为de且property1不为空的记录”，可以用@?操作符（检查是否存在匹配JSONPath路径的元素），或者jsonb_path_exists函数。

正确的SQL写法：

-- 使用@?操作符
SELECT id, json 
FROM xyz 
WHERE json @? '$.data.text1[?(@.lang == "de" && @.property1 != null)]';

-- 或者用jsonb_path_exists函数
SELECT id, json 
FROM xyz 
WHERE jsonb_path_exists(json, '$.data.text1[?(@.lang == "de" && @.property1 != null)]');

多属性查询支持 #

当然支持多条件组合查询，只要把多个JSONPath条件用逻辑操作符（AND/OR）组合起来就行。

比如你需要同时满足：

1. text1数组中存在lang为de且property1不为空的元素
2. text2数组中存在lang为de且property2等于"pqr"的元素

对应的SQL写法：

SELECT id, json 
FROM xyz 
WHERE 
  json @? '$.data.text1[?(@.lang == "de" && @.property1 != null)]'
  AND
  json @? '$.data.text2[?(@.lang == "de" && @.property2 == "pqr")]';

或者用jsonb_path_exists函数的写法：

SELECT id, json 
FROM xyz 
WHERE 
  jsonb_path_exists(json, '$.data.text1[?(@.lang == "de" && @.property1 != null)]')
  AND
  jsonb_path_exists(json, '$.data.text2[?(@.lang == "de" && @.property2 == "pqr")]');

内容的提问来源于stack exchange，提问作者talam