我正在嘗試將“custinfo”陣列決議為行，而不是我在查詢中的特定列（陣列中可以沒有或很多值）
DECLARE @json NVARCHAR(MAX) ='{
"customer": [
{
"id": "123",
"history": [
{
"id": "a123",
"dates": [
{
"date": "2022-03-19",
"details": {
"custinfo": [
"male",
"married"
],
"age": 40
}}]}]}]}'
SELECT
JSON_VALUE ( j.[value], '$.id' ) AS CustId,
JSON_VALUE ( m.[value], '$.id' ) AS CustId_Hist,
JSON_VALUE ( a1.[value], '$.date' ) AS date,
JSON_VALUE ( a1.[value], '$.details.age' ) AS age,
JSON_VALUE ( a1.[value], '$.details.custinfo[0]' ) AS custinfo0,
JSON_VALUE ( a1.[value], '$.details.custinfo[1]' ) AS custinfo1
FROM OPENJSON( @json, '$."customer"' ) j
CROSS APPLY OPENJSON ( j.[value], '$."history"' ) AS m
CROSS APPLY OPENJSON ( m.[value], '$."dates"' ) AS a1
期望的結果：
uj5u.com熱心網友回復：
就像我在評論中提到的那樣，我會切換到使用WITH子句并定義您的列及其資料型別。然后，您還可以使用以下內容將值放入您想要的 2 個單獨的行中。請注意最后的額外OPENJSON內容，它將custinfo視為陣列；回傳 2 行（陣列中的每個值 1 行）：
DECLARE @json NVARCHAR(MAX) ='{
"customer": [
{
"id": "123",
"history": [
{
"id": "a123",
"dates": [
{
"date": "2022-03-19",
"details": {
"custinfo": [
"male",
"married"
],
"age": 40
}}]}]}]}';
SELECT c.id AS CustId,
h.id AS CustId_Hist,
d.date AS date,
d.age AS age,
ci.[value] AS custinfo
FROM OPENJSON( @json,'$.customer')
WITH (id int,
history nvarchar(MAX) AS JSON) c
CROSS APPLY OPENJSON (c.history)
WITH (id varchar(10),
dates nvarchar(MAX) AS JSON) h
CROSS APPLY OPENJSON (h.dates)
WITH(date date,
details nvarchar(MAX) AS JSON,
age int '$.details.age') d
CROSS APPLY OPENJSON(d.details,'$.custinfo') ci;
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/470178.html