Sqlite.net JSON扩展语法

Question

我正在尝试理解Sqlite JSON扩展是如何工作的。假设一个公司有几个分支机构，每个分支机构都有一个库存目录，所以我创建了一个表，如下所示：

SQL = "CREATE TABLE BranchStock (branch VARCHAR, stock JSON)"
....

然后我插入几个项目，如：

SQL = "INSERT INTO BranchStock (branch, stock) values('Melbourne', json_array(json_object('catnumber', 'ABC123', 'instock',2), json_object('catnumber', 'BCD321', 'instock',5)))"
....

SQL = "INSERT INTO BranchStock (branch, stock) values('Sydney', json_array(json_object('catnumber', 'ABC123', 'instock',5), json_object('catnumber', 'XYZ567', 'instock',3)))"
....

现在我正在寻找搜索sql，以列出哪些分支具有catnumber='ABC123'，以及instock金额。可能是这样的：

SQL = "SELECT branch, json_extract(stock, '$[].instock') FROM BranchStock WHERE json_valid(stock) AND json_extract(stock, '$[].catnumber') = 'ABC123'

有人能帮我找出正确的SELECT语法吗？

提前感谢

Answer 1

注意:我并不建议对任何频繁运行或在大型表上运行的东西使用它，但是如果你必须这样做的话…

给定以下测试表：

CREATE TABLE BranchStock(branch TEXT, stock TEXT);
INSERT INTO BranchStock VALUES('Melbourne','[{"catnumber":"ABC123","instock":2},{"catnumber":"BCD321","instock":5}]');
INSERT INTO BranchStock VALUES('Sydney','[{"catnumber":"ABC123","instock":5},{"catnumber":"XYZ567","instock":3}]');

使用'ABC123'获取商店的分支机构和库存编号

SELECT branch, json_extract(value, '$.instock') AS instock
FROM BranchStock JOIN json_each(stock)
WHERE json_extract(value, '$.catnumber') = 'ABC123';

给出

branch      instock   
----------  ----------
Melbourne   2         
Sydney      5      

使用像这样的表定义来做几乎任何事情都会容易得多，也更高效

CREATE TABLE BranchStock(branch TEXT, catid TEXT, instock INTEGER);

而不是试图搜索存储在列中的JSON数据。例如：

SELECT branch, instock FROM BranchStock WHERE catid = 'ABC123';

尤其是当您开始向混合中添加索引时。