MBS Xojo Conference
6/7th September 2018, Munich

« Do you remember Bould… | Home | MBS Xojo Plugins, ver… »

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.
24 04 18 - 04:53
No comments

  
Remember personal info?

Emoticons / Textile


Notify:
Hide email:

Small print: All html tags except <b> and <i> will be removed from your comment. You can make links by just typing the url or mail-address.