基於 JSON 數據的多次插入觸發

我正在使用 sqlite 並且有它的 json1 擴展可用。我有以下表格（簡化為最小範例）：

-- 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;

"json_data"是以下形式：

{
   '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
}

這個實例"json_data"應該引起以下元組被插入"target" ( "node", "parent", "kind")：

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

通常，'clone_ids'會缺席；經常'parent_id'並且'ancestor_ids'將會缺席；甚至'id'可能缺席。因此，鑑於此，該方法應該是穩健的。

這種'child'是一種“升級” 'offspring'。我的感覺是，這次升級可以作為最後的更新來完成。

所以我的想法是我應該首先嘗試獲取表單的列表（作為 JSON 字元串或者可能是臨時表）

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

然後從此列表中創建可變數量的插入。

但是，我對 SQL 和 sqlite 的 json1 擴展非常缺乏經驗，所以我不知道（i）這是否可能，如果可以（ii）如何。所以我想得到對這兩個問題的答复。如果需要使用外部函式（我會在 Python 中完成）獲取後一個對列表，我仍然很感激有關如何從列表轉到插入的指導。

---

編輯：

在評論中，有人指出我的要求是不可能的。所以我想通過以下方式修改我的問題：假設我有一個生成以下 JSON 的使用者定義函式：

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

所以，基本上是我想要插入的元組。

我將如何插入它們？

這不是有效的 JSON；字元串需要雙引號。

無論如何，表值函式json_each()允許列舉一個動態大小的數組：

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]        $

為了從已知大小的數組中提取值，我們可以使用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

然後可以簡單地將其插入到 INSERT … SELECT … 語句中。

引用自：https://dba.stackexchange.com/questions/133583