sqlsqlitesqlite-json1

Setting value of boolean columns based on existence of value in set

I have a SQL table of the format (INTEGER, json_array(INTEGER)).

I need to return results from the table that have two boolean columns. One is set to true iff a 1 appears in the json_array, and the other true iff a two appears in the array. Obviously there is not mutual exclusion.

For example, if the data were this:

-------------------------------
| ID       |   VALUES         |
-------------------------------
|  12      |  [1, 4, 6, 11]   |
_______________________________
|  74      |  [0, 1, 2, 5]    |
-------------------------------

I would hope to get back:

-------------------------------
| ID       |   HAS1  |  HAS2  |
-------------------------------
|  12      |  true   | false  |
_______________________________
|  74      |  true   | true   | 
-------------------------------

I have managed to extract the json data out of the values column using json_each, but am unsure how to proceed.

Solution

  • If I recall correctly, SQLite max aggregate function supports boolean, therefore you can simply group by your data:

    select
    t1.id,
    max(case json_each.value when 1 then true else false end) as has1,
    max(case json_each.value when 2 then true else false end) as has2
from
    yourtable t1,
    json_each(t1.values)
group by
    t1.id