使用JSON DATA而不使用OPENJSON并按值搜索

Question

[
{
"Level"       : "S1",
"Major"       : "Teknik Informatika"
},
{
"Level"       : "SMA",
"Major"       : "IPA"
},
{
"Level"       : "SMP",
"Major"       : "Umum"
},
{
"Level"       : "SD",
"Major"       : "Umum"
}
]

iD  | UserID  | Education
------------------------------------------------------------------------------
1   | B000-1  | [{"Level":"S1","Major":"TI"},{"Level":"SMA","Major":"IPA"}]
2   | B000-2  | [{"Level":"SMA","Major":"IPS"},{"Level":"SD","Major":"Umum"}]
3   | B000-3  | [{"Level":"SMA","Major":"IPA"}]
4   | B000-4  | [{"Level":"SD","Major":"Umum"}]

Jika setap kolom表Educationkurang lebih berisi分隔对象数据，bagaimana saya menari数据Education[Level]='SMA'tanpa menggunakan OPENJSON, karena saya menggunakan sql server 2012

翻译:如果Education表中的每一列都包含上述对象，我如何在不使用OPENJSON的情况下找到Education[Level]='SMA'，因为我使用的是SQL Server 2012

iD  | UserID  | Education
------------------------------------------------------------------------------
1   | B000-1  | [{"Level":"S1","Major":"TI"},{"Level":"SMA","Major":"IPA"}]
2   | B000-2  | [{"Level":"SMA","Major":"IPS"},{"Level":"SD","Major":"Umum"}]
3   | B000-3  | [{"Level":"SMA","Major":"IPA"}]

Answer 1

快速搜索导致我在SQL Server中消费JSON字符串

这里作者展示了"旧"版本的SQL Server如何读取/写入JSON对象/字符串。解决方案需要根据您的需求进行定制，因为您的JSON结构不同，但这是可能的。

或者你可以蛮力like..如上文建议的PM 77-1

Answer 2

请尝试以下解决方案。

我们正在将JSON数据转换为基于XML的属性。例如，对于第一行，它将像这样:

<root>
<r Level="S1" Major="TI"/>
<r Level="NotSMA" Major="IPA"/>
</root>

之后使用XQuery方法.exist()搜索@Level参数。

/p>

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, Education NVARCHAR(MAX));
INSERT @tbl (Education) VALUES
(N'[{"Level":"S1","Major":"TI"},{"Level":"NotSMA","Major":"IPA"}]'),
(N'[{"Level":"SMA","Major":"IPS"},{"Level":"SD","Major":"Umum"}]'),
(N'[{"Level":"SMA","Major":"IPA"}]');
-- DDL and sample data population, end
DECLARE @separator CHAR(4) = '},{"'
, @Level VARCHAR(20) = 'SMA';
SELECT * 
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST('<root><r ' + 
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Education, '":', '=')
, ',"', SPACE(1))
, '}]', '')
, '[{"', ''), @separator, '/><r ') + 
'/></root>' AS XML)) AS t1(c)
WHERE c.exist('/root/r[@Level=sql:variable("@Level")]') = 1;

<表类>ID教育tbody><<tr>2[{"Level"SMA","Major":"IPS"},{"Level"SD","Major":"Umum"}]3[{"Level"SMA","Major":"IPA"}]