检索json数组中的数据

z9gpfhce  于 2021-07-26  发布在  Java
关注(0)|答案(1)|浏览(378)

我有以下sql“product”表结构:

int Id
nvarchar(max) Details

详细信息包含具有以下结构的字符串:

{
"Id": "10001",
"Description": "example description",
"Variants": [{
        "Title": "ABC / no",
        "Price": "10"
    }, {
        "Title": "ABC / Yes",
        "Price": "20",
    }, {
        "Title": "ABC / Yes",
        "Price": "30",
    }]
}

我需要编写一个sql查询来遍历表并返回所有具有特定标题的变量。
以下工作
从其详细信息字段包含特定标题的表中获取所有行

SELECT * FROM Products 
WHERE JSON_VALUE(Details, '$.Description') = 'example description'

从details.variants[0].title等于{string}的表中获取所有行

SELECT * FROM Products 
WHERE JSON_VALUE(Details, '$.Variants[0].Title') = 'ABC / no'

从details.variants[0].title等于{string}的表中获取所有ID

SELECT JSON_VALUE(Details, '$.Id') 
FROM Products 
WHERE JSON_VALUE(Details, '$.Variants[0].Title') = 'ABC / no'

我需要从product表的所有行中获取所有变量,其中变量标题等于{string}
在这个文档中有一个类似的例子,但我不能让它适用于我的特殊情况。
还有一个栈柱

hgb9j2n6

hgb9j2n61#

你需要使用 OPENJSON() 具有显式架构(列定义)和附加 APPLY s来解析输入json并获得预期结果。请注意,您需要使用 AS JSON 选项指定 $.Variants 存储的json的一部分是json数组。
表格:

CREATE TABLE Products (Id int, Details nvarchar(max))
INSERT INTO Products (Id, Details)
VALUES (1, N'{"Id":"10001","Description":"example description","Variants":[{"Title":"ABC / no","Price":"10"},{"Title":"ABC / Yes","Price":"20"},{"Title":"ABC / Yes","Price":"30"}]}"')

声明:

SELECT p.Id, j1.Id, j1.Description, j2.Title, j2.Price
FROM Products p
CROSS APPLY OPENJSON (p.Details, '$') WITH (
    Id int '$.Id',
    [Description] nvarchar(100) '$.Description',
    Variants nvarchar(max) '$.Variants' AS JSON
) j1
CROSS APPLY OPENJSON(j1.Variants) WITH (
    Title nvarchar(100) '$.Title',
    Price nvarchar(10) '$.Price'
) j2
WHERE 
   j2.Title = 'ABC / no'
   -- or j1.Description = 'example description'

结果:

Id  Id      Description         Title       Price
1   10001   example description ABC / no    10

相关问题