JSON到SQL-从JSON数组提取吗? [英] JSON to SQL - Extracting from JSON Array?

问题描述

我有一个练习，可以从一个较大的JSON对象中提取一些数据，但是这些数据会添加为多个对象，或者可能是一个数组.

I have an exercise to extract some data from a larger JSON object however the data is added as multiple objects or perhaps an array of sorts.

以下示例；

DECLARE @json NVARCHAR(MAX) = '{
"N.data.-ce731645-e4ef-4784-bc02-bb90b4c9e9e6": "Some Data",
"N.data.sessionDates-7f1790d3-9175-43aa-962b-161ee3b8615f": [
    {
        "date_1": "2018-10-20T23:00:00.000Z"
    },
    {
        "date_1": "2018-10-21T23:00:00.000Z"
    }
]
}'

我需要将这些datetime条目从"date_1"标识符中提取到理想的CSV列表中.由此，我可以进行自己的操作.

I need to extract these datetime entries from the "date_1" identifier into ideally a CSV list. From that I can do my own manipulations.

2018-10-20T23:00:00.000Z, 2018-10-21T23:00:00.000Z

我对JSON_VALUE()很熟悉，但是在简单的一维数据之外却不了解它.

I am familiar with JSON_VALUE() however not with its use outside of a simple piece of one dimensional data.

到目前为止我所拥有的；

What I have so far;

DECLARE @json NVARCHAR(MAX) = '{
"N.data.-ce731645-e4ef-4784-bc02-bb90b4c9e9e6": "Some Data",
"N.data.sessionDates-7f1790d3-9175-43aa-962b-161ee3b8615f": [
    {
        "date_1": "2018-10-20T23:00:00.000Z"
    },
    {
        "date_1": "2018-10-21T23:00:00.000Z"
    }
]
}'

SELECT value FROM OPENJSON(@json)

是否有办法在复杂的substring()和replace()使用之外获得预期的输出?

Is there a way to achive the expected output outside of complex substring() and replace() uses?

使用SQL Server 2017

Using SQL Server 2017

Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)   Aug 22 2017 17:04:49   Copyright (C) 2017 Microsoft Corporation  Express Edition (64-bit) on Windows Server 2012 R2 Datacenter 6.3 <X64> (Build 9600: ) (Hypervisor) 

谢谢

推荐答案

自SQL Server 2017起，可以通过本机OPENJSON进行提取:

Since SQL Server 2017, the extraction can be done via native OPENJSON:

DECLARE @json NVARCHAR(MAX) = '{
"N.data.sessionDates-7f1790d3-9175-43aa-962b-161ee3b8615f": [
    {
        "date_1": "2018-10-20T23:00:00.000Z"
    },
    {
        "date_1": "2018-10-21T23:00:00.000Z"
    }
]
}'


SELECT  
    JSON_VALUE(child_value.value, '$.date_1') AS [key]    
FROM OPENJSON(@json, '$') AS nda
cross apply openjson(nda.value, '$') as child_value

结果至:

key
2018-10-20T23:00:00.000Z
2018-10-21T23:00:00.000Z

是否有一种方法可以对此进行调整以提取特定值 密钥中的"N.data.sessionDates-7f1790d3-9175-43aa-962b-161ee3b8615f" 例子

Is there a way to adjust this to extract the values for a specific key, "N.data.sessionDates-7f1790d3-9175-43aa-962b-161ee3b8615f" in the example

在这种情况下，该查询可以稍微简化为:

In this case, that query can be slightly simplified to:

DECLARE @id nvarchar(200) = 'N.data.sessionDates-7f1790d3-9175-43aa-962b-161ee3b8615f'

SELECT  
    JSON_VALUE(nda.value, '$.date_1') AS [key]    
FROM OPENJSON(@json, concat('$."',@id,'"')) AS nda

或没有参数化:

SELECT  
    JSON_VALUE(nda.value, '$.date_1') AS [key]    
FROM OPENJSON(@json, '$."N.data.sessionDates-7f1790d3-9175-43aa-962b-161ee3b8615f"') AS nda

这篇关于JSON到SQL-从JSON数组提取吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！