I have a column type of jsonb named changes
[["change","is_enabled",[false,true]]]
This is a terrible schema. You're essentially storing an array of arrays. And what you're asking is how to query a jsonb-array, for any elements that are themselves an array that happen to have an array after a string named "is_enabled" which inside the array, has an element of true
.
test=# SELECT jsonb_pretty('[["change","is_enabled",[false,true]]]'::jsonb);
jsonb_pretty
-----------------------
[ +
[ +
"change", +
"is_enabled",+
[ +
false, +
true +
] +
] +
]
(1 row)
That's going to be extremely ugly. Nothing about this makes sense. Your JSON schema should just have one object that has {"is_enabled":true}
, or something like this {"name":"change","is_enable":true}
. As is, you need something very ugly,
You can unnest both JSON array's getting to the inside with relative ease. Then you'll have to mark the row after the is_enabled
element in the array,
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)
From this, it's simple using bool_or
, and another select
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;
We can add more rows to the VALUES
expression to test it
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)
If you just want the ones where bool_or IS TRUE
use HAVING
..
WHERE is_changed = true
GROUP BY j
HAVING bool_or(j2 @> 'true'::jsonb) IS TRUE;
[["change","is_enabled",[false,true]]]
or["change","is_enabled",[false,true]]
Is it a json array, or an array of arrays?is_enabled=true
?{"is_enabled": true}
...