In a single table paths are stored as JSON arrays, one per row; and a node may appear under more than one parent in different paths. I'm trying to update stored/historical paths as the user makes changes to the database, such as moving a node to under a different parent.

The best I can figure thus far is that I need to search for JSON arrays that have the node under its old parent; but I cannot search the path from root to the node because a node above its parent may appear under more than one parent. In this example, suppose node 7 currently under parent node 4 was just moved under a new parent. The old paths in the database that have a 7 under a 4 will need regenerated to reflect the new path, but cannot search for only an array that starts with 0,12,18,4,7 because node 18 may also be a child of another parent, such as node 5 as shown below, and it would be missed.

First question is, Is there a cleaner way to do this in Tcl using a user-defined function than as in this example?

package require sqlite3
3.52.0
% sqlite3 db ":memory:"
% db function in_json_array\
   -argcount 3 -deterministic -directonly -returntype integer\
   InJSONArray
% proc InJSONArray {jarray node parent } {
  set l [split [string range $jarray 1 end-1] ,]
  set i [lsearch -exact $l $node]
  return [expr {$i >= 0 && [lindex $l $i-1] == $parent}]
}
% db onecolumn {select in_json_array(json_array(0,12,18,4,7,11,32),7,4);}
1
% db onecolumn {select in_json_array(json_array(0,12,18,4,7,11,32),7,18);}
0
% db onecolumn {select in_json_array(json_array(0,5,18,4,7),7,4);}
1

Might be able to accomplish the same in SQLite only, using JSON_EACH, but that seems like a lot of work for the machine to split each JSON array into a table to determine if the row has a path that needs regenerated. For example,

.param set :node 7
.param set :parent 4

with 
j(k,v) as (
  select key, value
  from json_each(json_array(0,12,18,4,7,11,32))
)
select 1
from 
  j
where 
  k = (select k-1 from j where v = :node)
  and v = :parent
;
/*
╭───╮
│ 1 │
╞═══╡
│ 1 │
╰───╯
*/
.param set :node 7
.param set :parent 12

with 
j(k,v) as (
  select key, value
  from json_each(json_array(0,12,18,4,7,11,32))
)
select 1
from 
  j
where 
  k = (select k-1 from j where v = :node)
  and v = :parent
;
/* Empty */

Second question is, Is a user-defined function a lot of work for SQLite in passing the results of each row to the Tcl procedure, such that the table-valued function JSON_EACH would be more efficient?