Recursive SQLite CTE with JSON1 json_each

Question

I have a SQLite table where one column contains a JSON array containing 0 or more values. Something like this:

id|values
0 |[1,2,3]
1 |[]
2 |[2,3,4]
3 |[2]

What I want to do is "unfold" this into a list of all distinct values contained within the arrays of that column.

To start, I am using the JSON1 extension's json_each function to extract a table of values from a row:

SELECT
  value
FROM
  json_each(
      (
        SELECT
          values
        FROM
          my_table
        WHERE
          id == 2
      )
  )

Where I can vary the id (2, above) to select any row in the table.

Now, I am trying to wrap this in a recursive CTE so that I can apply it to each row across the entire table and union the results. As a first step I replicated (roughly) the results from above as follows:

WITH RECURSIVE result AS (
  SELECT null
  UNION ALL
  SELECT
    value
  FROM
      json_each(
          (
            SELECT
              values
            FROM
              my_table
            WHERE
              id == 2
          )
      )  
)
SELECT * FROM result;

As the next step I had originally planned to make id a variable and increment it (in a similar manner to the first example in the documentation, but haven't been able to get that to work.

I have gone through the other examples in the documentation, but they are somewhat more complex and I haven't been able to distill those down to see how they might apply to this problem.

Can someone provide a simple example of how to solve this (or a similar problem) with a recursive CTE?

Of course, my goal is to solve the problem with or without CTEs so Im also happy to hear if there is a better way...

Answer 1

You do not need a recursive CTE for this.

To call json_each for multiple source rows, use a join:

SELECT t1.id, t2.value
FROM my_table AS t1
JOIN json_each((SELECT "values" FROM my_table WHERE id = t1.id)) AS t2;