时间戳

首页 > 解决方案 > How to extract a part of a JSON String from an SQL string Base64

sql - How to extract a part of a JSON String from an SQL string Base64

问题描述

Situation:

I have a column of encoded Base64 strings that I would like to decode and extract a specific part of it. It seems the decoded value is in JSON format (not familiar with JSON at all)

Objective:

How can I extract the value of a dictionary part of a JSON string.

Current query:

SELECT 
    CONVERT(VARCHAR(MAX),CAST('' AS XML).value('xs:base64Binary(sql:column("BASE64_COLUMN"))', 'VARBINARY(MAX)')) AS RESULT
FROM
    (SELECT [value] AS BASE64_COLUMN FROM #test1) as testtable

Output:

The output looks like this:

RESULT
{"a":1,"b":2,"c":3,"d":4}

Desired Output: How can i extract the value of the first dictionary: 1 ?

标签： sqljsonsql-serverxmlbase64

解决方案

我觉得我们这里没有所有的部分，但是，使用那个 JSON 字符串，你可以'a'通过简单地使用来获取键的值OPENJSON：

DECLARE @JSON nvarchar(MAX) = N'{"a":1,"b":2,"c":3,"d":4}';

SELECT [value]
FROM OPENJSON(@JSON)
WHERE [key] = 'a';

当然，我假设您使用的是 SQL Server 2016。否则 SQL Server 不支持 JSON 解析，您最好使用应用程序来执行此操作。

对于 2014-，如果您只想要第一个节点的值，那么您可以这样做

SELECT V.JSON, SUBSTRING([JSON],CHARINDEX(':',[JSON])+1,CHARINDEX(',',[JSON])-(CHARINDEX(':',[JSON])+1))
FROM (VALUES(@JSON))V([JSON]);

因此，对于您的表的盲目猜测（因为我无法测试）：

SELECT SUBSTRING([JSONString],CHARINDEX(':',[JSONString])+1,CHARINDEX(',',[JSONString])-(CHARINDEX(':',[JSONString])+1))
FROM (SELECT [value] AS BASE64_COLUMN FROM #test1) as testtable
     CROSS APPLY (VALUES(CONVERT(VARCHAR(MAX),CAST('' AS XML).value('xs:base64Binary(sql:column("BASE64_COLUMN"))', 'VARBINARY(MAX)')))V(JSONString);

这假定表达式中的每个字符串都包含至少1 个:字符和,字符。如果没有，那么我们需要更多（代表性）样本数据。

推荐阅读