Use JSON functions with SQLite

In both MBS FileMaker Plugin and in the MBS Xojo SQL Plugin, we have a built-in SQLite library with various extensions. You activate it in FileMaker with SQL.InternalSQLiteLibrary.Activate and in Xojo with methods in InternalSQLiteLibraryMBS module.

Once you use a SQLite library with JSON functions, you can use seventeen new functions from the json1 extension that are useful for managing JSON content stored in an SQLite database. Twelve of the fourteen SQL functions are scalar functions:

json(json)
json_array(value1,value2,...)
json_array_length(json)
json_array_length(json,path)
json_extract(json,path,...)
json_insert(json,path,value,...)
json_object(label1,value1,...)
json_patch(json1,json2)
json_remove(json,path,...)
json_replace(json,path,value,...)
json_set(json,path,value,...)
json_type(json)
json_type(json,path)
json_valid(json)
json_quote(value)

There are two aggregate SQL functions:

json_group_array(value)
json_group_object(name,value)

The two table-valued functions are:

json_each(json)
json_each(json,path)
json_tree(json)
json_tree(json,path)

e.g. in a query you can use the function like in the following example:

SELECT json_array_length('[1,2,3,4]')

Or

SELECT * FROM MyTable WHERE json_extract(JSON, '$.type') = "person"

Could be useful for projects, where you store a JSON blob in a field and you like to query against it. Claris FileMaker Plugin