如何在jsonb列內查詢

我有一個名為changes的 jsonb 列類型

這是我的專欄的結構。

[["change","is_enabled",[false,true]]]

我想在列內查詢。

我想找到 “is_enabled” = true 的所有行

我已經嘗試了一切。你能幫我找到解決辦法嗎

我有一個名為 changes 的 jsonb 列類型

[["change","is_enabled",[false,true]]]

這是一個可怕的模式。您實際上是在儲存一個數組數組。而您要問的是如何查詢 jsonb 數組，對於任何本身就是數組的元素，這些元素恰好在名為“is_enabled”的字元串之後有一個數組，該字元串在數組內部有一個true.

test=# SELECT jsonb_pretty('[["change","is_enabled",[false,true]]]'::jsonb);
    jsonb_pretty      
-----------------------
[                    +
    [                +
        "change",    +
        "is_enabled",+
        [            +
            false,   +
            true     +
        ]            +
    ]                +
]
(1 row)

這將是非常醜陋的。這沒有任何意義。您的 JSON 模式應該只有一個具有{"is_enabled":true}或類似的對象{"name":"change","is_enable":true}。事實上，你需要一些非常醜陋的東西，

您可以相對輕鬆地解除兩個 JSON 數組的嵌套。然後你必須is_enabled在數組中的元素之後標記行，

SELECT *,
 '"is_enabled"'::jsonb = lag(j2) OVER (PARTITION BY j1 ORDER BY o) AS is_changed
FROM ( VALUES
 ($$[["change","is_enabled",[false,true]]]$$::jsonb)
) AS t(j)
CROSS JOIN LATERAL jsonb_array_elements(j)
 AS jae1(j1)
CROSS JOIN LATERAL jsonb_array_elements(j1) WITH ORDINALITY
 AS jae2(j2,o);
                    j                     |                   j1                    |      j2       | o | is_changed 
-------------------------------------------+-----------------------------------------+---------------+---+------------

[["change", "is_enabled", [false, true]]] | ["change", "is_enabled", [false, true]] | "change"      | 1 | 
[["change", "is_enabled", [false, true]]] | ["change", "is_enabled", [false, true]] | "is_enabled"  | 2 | f
[["change", "is_enabled", [false, true]]] | ["change", "is_enabled", [false, true]] | [false, true] | 3 | t
(3 rows)

由此，使用很簡單bool_or，另一個選擇

SELECT j, bool_or(j2 @> 'true'::jsonb)
FROM (
 SELECT j, j2,
  '"is_enabled"'::jsonb = lag(j2) OVER (PARTITION BY j1 ORDER BY o) AS is_changed
 FROM ( VALUES
   ($$[["change","is_enabled",[false,true]]]$$::jsonb)
 ) AS t(j)
 CROSS JOIN LATERAL jsonb_array_elements(j)
   AS jae1(j1)
 CROSS JOIN LATERAL jsonb_array_elements(j1) WITH ORDINALITY
   AS jae2(j2,o)
) AS t
WHERE is_changed = true
GROUP BY j;

VALUES我們可以在表達式中添加更多行來測試它

SELECT j, bool_or(j2 @> 'true'::jsonb)
FROM (

 SELECT j, j2,
  '"is_enabled"'::jsonb = lag(j2) OVER (PARTITION BY j1 ORDER BY o) AS is_changed
 FROM ( VALUES
   ($$[["change","is_enabled",[false,false]]]$$::jsonb),
   ($$[["change","is_enabled",[true,false]]]$$::jsonb),
   ($$[["change","is_enabled",[true,true]]]$$::jsonb)
 ) AS t(j)
 CROSS JOIN LATERAL jsonb_array_elements(j)
   AS jae1(j1)
 CROSS JOIN LATERAL jsonb_array_elements(j1) WITH ORDINALITY
   AS jae2(j2,o)

) AS t
WHERE is_changed = true
GROUP BY j;

                    j                      | bool_or 
--------------------------------------------+---------
[["change", "is_enabled", [true, false]]]  | t
[["change", "is_enabled", [true, true]]]   | t
[["change", "is_enabled", [false, false]]] | f
(3 rows)

如果你只想要那些bool_or IS TRUE使用HAVING..

WHERE is_changed = true
GROUP BY j
HAVING bool_or(j2 @> 'true'::jsonb) IS TRUE;

引用自：https://dba.stackexchange.com/questions/209976