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

Antler

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"}

Antler

Is there a way you can do a select statement based on a value in the json field? – Frogfish

something like select json_extract(user.phone, '$.cell = 491765') from user; – Frogfish

@Oz123 what if I have nested json, is it still possible? – Lowering

What if I don't know the name of the key? Is there some way to get the name of the first key and value of an object? – Telephotography

@Telephotography json_each / json_tree ? – Maidenhood

what should be the type for column that accepts json? (for above example, type for phone column) is Text type fine? – Fulton

Text type is fine @EricAhn – Naevus

@ThePaxBisonica you can write queries like select people.data from people where json_extract(people.data, '$.age') > 29, where data is a TEXT column with a JSON value containing the age property as an integer. – Naevus