Unesting JSON array in SQLite [closed]

Question

I'm using DB Manager on QGIS to write a view. I want to "translate" unnest() SQL function, but I don't find the same in SQLite.

SQL

SELECT unnest(ARRAY['A','B','C']);

unnest
A
B
C

The documentation describes two aggregate functions and two table-valued functions. I tried json_each, which returns a row for each json value but I can't display unnested values.

SQLite

SELECT myfield
FROM my_table, json_each(my_field);

my_field
["A", "B", "C" ]
["A", "B", "C" ]
["A", "B", "C" ]

How to easily unnest json elements in SQLite?

Answer 1

As @geozelot suggests, the only way I found is a recursive CTE.

WITH RECURSIVE
  unnest(val, arr_el, _arr_res) AS (
    SELECT
      myfield,
      '',
      myfield || ','
    FROM
      my_table
    UNION ALL
    SELECT
      val,
      SUBSTR(_arr_res, 0, INSTR(_arr_res, ',')),
      SUBSTR(_arr_res, INSTR(_arr_res, ',') + 1)
    FROM
      unnest
    WHERE
      _arr_res <> ''
  )
SELECT
  REPLACE(REPLACE(REPLACE(arr_el, '[', ''), ']', ''), '"', '') AS arr_el,
  val
FROM
  unnest
WHERE
  arr_el <> ''
ORDER BY
  val, arr_el
;

Really heavy to manipulate, I'm surprised there are aggregates functions and no easy way to disaggregate.

Answer 2

The json_each function returns the desired values in the value column:

create table my_table(id, myfield);
insert into my_table values (1, '["A","B","C"]'), (2, '["X"]');

SELECT my_table.id, j.value
FROM my_table, json_each(myfield) j;

id | value
----------
1  | A
1  | B
1  | C
2  | X