我有一個名為Order的表，其jsonb列型別名為line_items。line_items列可以包含這樣的嵌套值：
[
{
"id":9994857545813,
"sku":"CLIPPING-PATH_C2_24H",
},
{
"id":9994857578581,
"sku":"NATURAL-SHADOW_C1_24H",
}
]
上面的示例中有兩個訂單項，但它可以從 1 到任意數量的訂單項不等。
我需要查詢僅包含 1 個訂單項的所有訂單，其中 sku = 特定值，例如CLIPPING-PATH_C2_24H，如上例所示。
因此，查詢不應與上述示例匹配，而是以下僅包含 1 個行專案且 sku=CLIPPING-PATH_C2_24H
[
{
"id":9994857545813,
"sku":"CLIPPING-PATH_C2_24H",
}
]
可以幫助使用 Rails 活動記錄撰寫查詢嗎？
uj5u.com熱心網友回復：
演示
您可以在 ruby?? 中呼叫 plpghsql。（如何從 Ruby on rails 呼叫 plpgsql 函式？）
sql查詢：select jsonb_path_query(order_json,'$[*] ? (@.sku == "CLIPPING-PATH_C2_24H")') from orders ;
正確處理并不容易，因為 PL/pgsql 函式字串的某些部分甚至包含 4 個單引號。最好使用 raise notice 來逐步測驗。
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;
叫它：select * from get_sku_CLIPPING_path('"CLIPPING-PATH_C2_24H"');
uj5u.com熱心網友回復：
首先，您沒有嵌套的 json。你只有一個帶有物件的 json 陣列。此外，您的 json 物件可以表示為表格。最好將這些物件存盤在另一個表中并設定一對多關系。你可以有一個“orders”表和一個“order_details”表。
要獲取您需要的資料，首先我們需要找到記錄，"sku":"CLIPPING-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"}]';
結果會是這樣；
編號 | 庫存單位 ------------- | -------------------- 9994857545813 | 剪輯路徑_C2_24H
小提琴在這里
uj5u.com熱心網友回復：
我能夠使用 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
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/482393.html
上一篇：帶有前端引擎的Rails5API
下一篇：為什么我在加載名稱空間固定裝置時收到ActiveRecord::Fixture::FixtureError錯誤？