Trigger with multiple inserts based on JSON data

Question

I am using sqlite and have its json1 extension available. I have the following tables (reduced to a minimal example):

-- table filled from external source,
-- with only a little possible impact on data
create table "source" (
    "id" Integer primary key,
    "json_data" Text not NULL
);

-- table to be filled using a trigger on "source"
create table "target" (
    "node" Text primary key,
    "parent" Text, -- NULL when root of a tree
    "kind" Text  -- NULL when "parent" is NULL
        check ("kind" in ('clone', 'child', 'offspring'))
) without rowid;

The "json_data" is of the following form:

{
    'id': [n0]  // single-component list
    'clone_ids': ['c2', 'c1'],  // multi-component list
    'parent_id': ['p0'],  // single-component list
    'ancestor_ids': ['a1, a2, p0']
        // single-component list, but multiple semantic components
}

This instance of "json_data" should give rise to the following tuples being inserted into "target" ( "node", "parent", "kind"):

'c2', 'c1', 'clone'
'c1', 'n0', 'clone'
'n0', 'p0', 'child'
'p0', 'a2', 'offspring'
'a2', 'a1', 'offspring'
'a1', NULL, NULL

Usually, 'clone_ids' will be absent; often 'parent_id' and 'ancestor_ids' will be absent; even 'id' could be absent. So the approach should be robust in view of this.

The 'child' kind is an ‘upgrade’ from the 'offspring' one; my feeling was that this upgrade could be done as an update at the end.

So my idea was that I should first try and get a list (as a JSON string or perhaps a temporary table) of the form

[
    ["c2", "clone"],
    ["c1", "clone"],
    ["n0", "offspring"],
    ["p0", "offspring"],
    ["a2", "offspring"],
    ["a1", null]
]

and then create the variable number of inserts from this list.

However, I'm quite inexperienced with SQL and sqlite's json1 extension, so I do not know (i) whether this is possible, and if yes (ii) how. So I would like to get responses to both questions. In case getting the latter list of pairs needs to be done using an external function (which I would do in Python), I still appreciate guidance on how to go from the list to the inserts.

---

EDIT:

In a comment it has been indicated that what I asked is not possible. So I would like to amend my question in the following way: Assume I have a user-defined function that produces the following JSON:

[
    ["c2", "c1", "clone"],
    ["c1", "n0", "clone"],
    ["n0", "p0", "offspring"],
    ["p0", "a2", "offspring"],
    ["a2", "a1", "offspring"],
    ["a1", null, null]
]

So, essentially the tuples I want to insert.

How would I go about inserting them?

Answer 1

This is not valid JSON; strings require double quotes.

Anyway, the table-valued function json_each() allows to enumerate a dynamically-sized array:

SELECT * FROM json_each('[["c2","c1","clone"],["c1","n0","clone"],["n0","p0","offspring"],["p0","a2","offspring"],["a2","a1","offspring"],["a1",null,null]]');
key         value                type        atom        id          parent      fullkey     path
----------  -------------------  ----------  ----------  ----------  ----------  ----------  ----------
0           ["c2","c1","clone"]  array                   1                       $[0]        $
1           ["c1","n0","clone"]  array                   5                       $[1]        $
2           ["n0","p0","offspri  array                   9                       $[2]        $
3           ["p0","a2","offspri  array                   13                      $[3]        $
4           ["a2","a1","offspri  array                   17                      $[4]        $
5           ["a1",null,null]     array                   21                      $[5]        $

For extracting the values from an array with a known size, we can use json_extract():

SELECT json_extract(value, '$[0]'), json_extract(value, '$[1]'), json_extract(value, '$[2]')
FROM json_each('[["c2","c1","clone"],["c1","n0","clone"],["n0","p0","offspring"],["p0","a2","offspring"],["a2","a1","offspring"],["a1",null,null]]');
c2          c1          clone
c1          n0          clone
n0          p0          offspring
p0          a2          offspring
a2          a1          offspring
a1

This can then simply be plugged into an INSERT … SELECT … statement.