4

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?

Improve this question
4
  • 1
    Only way is a recursive CTE: gis.stackexchange.com/a/460107/93656 Commented May 31, 2023 at 14:09
  • 1
    In SQLite there is no such function: unnest Commented May 31, 2023 at 14:12
  • If you are referring to the unnest in my linked answer, that's not a function but a virtual table name of the recursive CTE expresion that mimics an unnest. However, you may want to try the json_tree variant to also walk children. If that doesn't unnest the array values of your JSON, you have to use the recursive CTE as in my linked answer. And you will have to use the recursive CTE if your array is not a valid JSON in the first place... Commented May 31, 2023 at 14:26
  • Ok, understand. Commented May 31, 2023 at 15:11

2 Answers 2

Reset to default
5

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.

Improve this answer
3

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
Improve this answer
1
  • Great, exactly what I was looking for. Commented Jun 1, 2023 at 8:33

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.