JSON 提取\集的 SQLite JSON1 示例
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33432421/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQLite JSON1 example for JSON extract\set
提问by Oz123
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.
SQLite 现在有一个实验性的 JSON1 扩展来处理 JSON 字段。可供选择的函数看起来很有希望，但我不知道如何在查询的上下文中使用它们。
Suppose I created the following table:
假设我创建了下表：
sqlite> create table user(name,phone);
sqlite> insert into user values('oz', json_array(['+491765','+498973']));
The documentationshows how to use json_eachin a query, but all other functions lack some in context documentation.
该文档显示了如何json_each在查询中使用，但所有其他函数都缺少一些上下文文档。
Can someone with SQLite experience provide a few examples of how to use:
有 SQLite 经验的人能否提供一些如何使用的示例：
json_extractjson_set
json_extractjson_set
回答by Oz123
So, here is a first example of how to use json_extract. First, the data is a inserted in a bit different way:
因此，这是如何使用json_extract. 首先，数据是以一种有点不同的方式插入的：
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:
现在，我们可以像在普通 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:
但是，如果我们不关心固定电话而只想要手机呢？
输入json_extract：
sqlite> select json_extract(user.phone, '$.cell') from user;
+491765
And this is how to use json_extract.
这就是如何使用json_extract.
Using json_setis similar. Given that the we want to update the cell phone:
使用json_set方法类似。鉴于我们要更新手机：
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.
您可以在其他 SQL 查询中组合这些函数调用。因此，您可以将 SQLite 用于结构化数据和 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"}
