SQLite JSON1 example for JSON extract\set

Question

SQLite has now an experimental JSON1 extension to work with JSON fields. The functions to choose from look promising, but I don't get how to use them in the context of a query.

Suppose I created the following table:

sqlite> create table user(name,phone);
sqlite> insert into user values('oz', json_array(['+491765','+498973']));

The documentation shows how to use json_each in a query, but all other functions lack some in context documentation.

Can someone with SQLite experience provide a few examples of how to use:

• json_extract
• json_set

Answer 1

So, here is a first example of how to use json_extract. First, the data is a inserted in a bit different way:

insert into user (name, phone) values("oz", json('{"cell":"+491765", "home":"+498973"}'));

Now, we can select all the users phone numbers as in normal sql:

sqlite> select user.phone from user where user.name=='oz';
{"cell":"+491765","home":"+498973"}
sqlite> 

But, what if we don't care about land lines and we want only cell phones?
Enter json_extract:

sqlite> select json_extract(user.phone, '$.cell') from user;
+491765

And this is how to use json_extract.

Using json_set is similar. Given that the we want to update the cell phone:

sqlite> select json_set(user.phone, '$.cell', 123) from \
        user;
{"cell":123,"home":"+498973"}

You can combine those function calls in other SQL queries. Thus, you can use SQLite with structured data and with unstructured data in the form of JSON.

Here is how to update the user cell phone only:

sqlite> update user 
   ...> set phone =(select json_set(user.phone, '$.cell', 721) from user)
   ...> where name == 'oz';
sqlite> select * from user;
oz|{"cell":721,"home":"+498973"}