SQLite JSON1示例：用于JSON提取/设置

Question

SQLite现在有一个实验性的JSON1扩展，可以处理JSON字段。可供选择的函数看起来很有前途，但我不知道如何在查询的上下文中使用它们。

假设我创建了以下表：

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

文档展示了如何在查询中使用json_each，但其他所有函数都缺少一些上下文文档。

有SQLite经验的人可以提供一些如何使用以下函数的示例：

• json_extract
• json_set

- oz123

Answer 1

所以，这是如何使用json_extract的第一个例子。首先，数据以稍微不同的方式插入：

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

现在，我们可以像普通的 SQL 一样选择所有用户的电话号码：

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

但是，如果我们不关心传统的电话线路，只想要移动电话呢？
输入json_extract：

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

这是如何使用json_extract的方法。

使用json_set也类似。假设我们想要更新手机信息：

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

您可以在其他SQL查询中组合这些函数调用。因此，您可以将SQLite与结构化数据和以JSON形式的非结构化数据一起使用。

以下是仅更新用户手机的方法：

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

- oz123

---

您是否可以根据JSON字段中的值执行选择语句？ - The Pax Bisonica

从user中选择json_extract(user.phone, '$.cell = 491765')。 - The Pax Bisonica

@Oz123 如果我有嵌套的json，是否仍然可能？ - Keselme

如果我不知道键的名称怎么办？有没有一种方法可以获取对象的第一个键和值的名称？ - Michael

@ThePaxBisonica，您可以编写查询语句，例如select people.data from people where json_extract(people.data, '$.age') > 29。其中，data是一个TEXT列，其JSON值包含整数类型的age属性。 - Rodolfo Gonçalves