0

I have a column type of jsonb named changes

THis is the structure of my column.

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

I want to query within the column.

I want to find all the rows that "is_enabled" = true

I have tried everything. Can you please help me find a solution

5
  • Is changes = [["change","is_enabled",[false,true]]] or ["change","is_enabled",[false,true]] Is it a json array, or an array of arrays? Jun 18 '18 at 23:50
  • Wat does it mean to have is_enabled=true? Jun 18 '18 at 23:52
  • @EvanCarroll It is [["change","is_enabled",[false,true]]] and I want to query for when [["change","is_enabled_in_client",[false,true]]] and NOT [["change","is_enabled_in_client",[false,false]]]
    – noobeerp
    Jun 18 '18 at 23:58
  • But you're not even using an object {"is_enabled": true}... Jun 19 '18 at 0:03
  • @EvanCarroll maybe I framed my question wrong. But i am not sure on how to go about this in PostgreSQL. I know how to do this in Spark SQL. I would've just used LATERAL VIEW EXPLODE. But i am lost on how to work on it in PostgreSQL
    – noobeerp
    Jun 19 '18 at 0:06
1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.