对数组中的嵌套对象进行 jsonb LIKE 查询

我的 JSON 数据如下所示：

[{
  "id": 1,
  "payload": {
    "location": "NY",
    "details": [{
            "name": "cafe",
            "cuisine": "mexican"
        },
        {
            "name": "foody",
            "cuisine": "italian"
        }
    ]
  }
}, {
  "id": 2,
  "payload": {
    "location": "NY",
    "details": [{
            "name": "mbar",
            "cuisine": "mexican"
        },
        {
            "name": "fdy",
            "cuisine": "italian"
        }
    ]
  }
}]

给定文本“foo”，我想返回具有该子字符串的所有元组。但我不知道如何编写相同的查询。

我跟着这个相关答案 https://stackoverflow.com/questions/29108179/jsonb-query-with-nested-objects-in-an-array但不知道该怎么做LIKE.
这就是我现在正在做的工作：

SELECT r.res->>'name' AS feature_name, d.details::text
  FROM   restaurants r
    , LATERAL (SELECT ARRAY (
     SELECT * FROM json_populate_recordset(null::foo, r.res#>'{payload, 
       details}')
     )
   ) AS d(details)
 WHERE d.details @> '{cafe}';

而不是传递整个文本cafe我想通过ca并获得与该文本匹配的结果。

您的解决方案可以进一步简化：

SELECT r.res->>'name' AS feature_name, d.name AS detail_name
FROM   restaurants r
     , jsonb_populate_recordset(null::foo, r.res #> '{payload, details}') d
WHERE  d.name LIKE '%oh%';

或者更简单，但是，jsonb_array_elements() https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE因为您实际上不需要行类型（foo）在这个例子中：

SELECT r.res->>'name' AS feature_name, d->>'name' AS detail_name
FROM   restaurants r
     , jsonb_array_elements(r.res #> '{payload, details}') d
WHERE  d->>'name' LIKE '%oh%';

数据库小提琴

但那是not你什么asked确切地：

我想返回所有具有该子字符串的元组。

您将返回所有 JSON 数组元素（每个基表行 0-n），其中一个特定键 ('{payload,details,*,name}') 匹配（区分大小写）。

您最初的问题在此之上有一个嵌套的 JSON 数组。您删除了此解决方案的外部数组 - 我也做了同样的事情。

根据您的实际需求Postgres 10 的新文本搜索功能 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=e306df7f9cd6b4433273e006df11bdc966b7079e可能有用。