JSON extract \ set的SQLite JSON1示例 [英] SQLite JSON1 example for JSON extract\set

问题描述

SQLite现在具有实验性的JSON1扩展，可与JSON字段一起使用.从中选择的功能看起来很有希望，但是我不知道如何在查询的上下文中使用它们.

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> create table user(name,phone);
sqlite> insert into user values('oz', json_array(['+491765','+498973']));

文档显示了如何在查询中使用json_each，但还有其他所有功能缺少一些上下文文档.

The documentation shows how to use json_each in a query, but all other functions lack some in context documentation.

具有SQLite经验的人可以提供一些使用示例吗?

Can someone with SQLite experience provide a few examples of how to use:

• json_extract
• json_set

• json_extract
• json_set

推荐答案

因此，这是如何使用json_extract的第一个示例.首先，以不同的方式插入数据:

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中一样选择所有用户的电话号码:

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> 

但是，如果我们不关心固定电话而只想要手机，该怎么办?
输入json_extract:

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

这是使用json_extract的方法.

使用json_set是相似的.鉴于我们要更新手机:

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

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

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

这篇关于JSON extract \ set的SQLite JSON1示例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！