Postgresql – How to query inside jsonb column

jsonpostgresqlpostgresql-9.3postgresql-9.4

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

Best Answer

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;
Related Question