SQLite现在有一个试验性的JSON1扩展来处理JSON字段.可供 Select 的函数看起来很有前途，但是我不知道如何在查询上下文中使用它们.
假设我创建了下表:
sqlite> create table user(name,phone);
sqlite> insert into user values('oz', json_array(['+491765','+498973']));
documentation显示了如何在查询中使用json_each，但所有其他函数都缺少一些上下文文档.
Can someone with SQLite experience provide a few examples of how to use:
json_extractjson_set个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"}'));
现在，我们可以像在普通sql中一样 Select 所有用户的电话号码:
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"}