sqlite3的json1扩展的函数_sqlite json1拓展插入数组-优快云博客

本文介绍如何使用SQLite内置的JSON函数进行数据操作，包括插入、选择和更新JSON数据。通过具体示例展示了如何仅提取特定JSON字段，以及如何更新JSON字段中的值。

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(json(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(json(user.phone), '$.cell', 721) from user)
   ...> where name == 'oz';
sqlite> select * from user;
oz|{"cell":721,"home":"+498973"}