如何查询铁轨中的嵌套JSONB Postgres

Question

我有一个称为 order>的表，带有 jsonb 列类型，称为 line_items 。 line_items 列可以包含类似的嵌套值：

[
  {
    "id":9994857545813,
    "sku":"CLIPPING-PATH_C2_24H",
  },
  {
    "id":9994857578581,
    "sku":"NATURAL-SHADOW_C1_24H",
  }
]

上面的示例中有两个订单项，但它可以从1到任何数量的行项目不等。

我需要查询所有仅包含1个订单项的订单，其中sku =特定值，例如剪切path_c2_24h 按照上述示例。

因此，查询不应匹配上述示例，但以下示例只有1个订单项，而SKU =剪切path_c2_24h

[
  {
    "id":9994857545813,
    "sku":"CLIPPING-PATH_C2_24H",
  }
]

可以使用Rails Active Record编写查询吗？

Answer 1

我能够使用jsonb_array_length方法来弄清楚这一点：

Order
.where("line_items @> ?", [{sku: sku}].to_json)
.where("jsonb_array_length(line_items) = 1")
.count
end

以下资源非常有帮助：
https://gist.github.com/mankind/1802dbb64fc24be33d434d593afd6221

Answer 2

）

---

SQL查询：选择JSONB_PATH_QUERY（order_json，'$ [*]？（@.sku ==“ clipping path_c2_24h''）从订单中;

---

都不容易为了使它正确，因为PL/PGSQL功能字符串的某些部分甚至包含4个单语引号。更好地使用加薪通知来逐步测试它。

CREATE OR REPLACE FUNCTION get_sku_CLIPPING_path (_sku text)
    RETURNS json
    AS $
DECLARE
    _sql text;
    _returnjson jsonb;
BEGIN
    RAISE NOTICE '%: _sku', $1;
    RAISE NOTICE '%', '$[*] ? (@.sku == ' || $1 || ')';
    RAISE NOTICE '%', $s$
    SELECT
        jsonb_path_query(order_json, $s$ || '''' || '$[*] ? (@.sku == ' || $1 || ')''' || ' from orders';
    _sql := $s$
    SELECT
        jsonb_path_query(order_json, $s$ || '''' || '$[*] ? (@.sku == ' || $1 || ')''' || ' )from orders';
    EXECUTE _sql
    USING _sku INTO _returnjson;
    RETURN (_returnjson::json);
END
$
LANGUAGE plpgsql;

调用：选择 *从get_sku_clipping_path（'“剪辑path_c2_24h”'）;

Answer 3

首先，您没有嵌套的JSON。您只有一个带有对象的JSON数组。而且，您的JSON对象可以表示为表格。最好将这些对象存储在另一个表中，然后将一个对象设置为一个关系。
您可以有一个“订单”表和“ order_details”表。

要获取您需要的数据，首先我们需要查找具有“ sku”的记录：“剪切path_c2_24h” 然后我们需要解析JSON并从line_items字段中获取该对象。

SELECT
    t.*
  FROM orders o,
  -- extract object from array of json objects 
  LATERAL jsonb_path_query(o.line_items, '$[*] ? (@.sku == $value)', '{"value" : "CLIPPING-PATH_C2_24H"}') order_line,
  -- convert "sku":"NATURAL-SHADOW_C1_24H" into columns 
  LATERAL jsonb_to_record(order_line) as t(id bigint, sku text)
  WHERE
    -- find record which has "sku":"NATURAL-SHADOW_C1_24H"
    o.line_items @> '[{"sku":"NATURAL-SHADOW_C1_24H"}]';

结果就是这样；

           id | sku                 
------------- | --------------------
9994857545813 | CLIPPING-PATH_C2_24H

小提琴是