时间戳

首页 > 解决方案 > 在 SQL 中提取 JSON 变量中的嵌套数据

sql - 在 SQL 中提取 JSON 变量中的嵌套数据

问题描述

我需要一些帮助来从 SQL 中的 JSON 变量中提取我需要的数据：

JSON 树

我需要在表中获取 management_account_id、Month 和 cost_to_client/details，我面临的问题是月份在层次结构中高于 details 部分，所以我要进入每个单独的月份来访问 details 对象，下面是我的代码：

/* management_account_id, [cost_to_client] 8 fields */

SELECT table2.ACCID, 
h3.[Management fees received],
h3.[Ad hoc invoices]
FROM OPENJSON(@WP_ACCOUNT, '$.result') j1
OUTER APPLY OPENJSON(j1.[value]) WITH (
    ACCID nvarchar(50) '$.management_account_id',
    cost_to_client nvarchar(max) '$.cost_to_client' as JSON
) table2

CROSS APPLY OPENJSON(table2.cost_to_client)
    with(
        [December 2020] nvarchar(max) as JSON,
        [January 2021] nvarchar(max) as JSON,
        [February 2021] nvarchar(max) as JSON,
        [March 2021] nvarchar(max) as JSON,
        [April 2021] nvarchar(max) as JSON,
        [May 2021] nvarchar(max) as JSON,
        [June 2021] nvarchar(max) as JSON,
        [July 2021] nvarchar(max) as JSON
    ) h1

/* Level 2: Month expanded  */

CROSS APPLY OPENJSON(h1.[December 2020])
    with(
        details nvarchar(max) as JSON
    ) h2

/* Level 3: Month/details expanded  */

CROSS APPLY OPENJSON(h2.details)
    with(
        [Management fees received] nvarchar(50),
        [Ad hoc invoices] nvarchar(50)
    ) h3
;

这就是我得到的：

数据片段

我想要的是下面包含所有月份的内容：

输出

JSON文本如下：

{
    "result": {
        "12183": {
            "management_account_id": "12183",
            "label": "The Zone",
            "waste_stream_statistics": [],
            "rebates": [],
            "cost_to_client": {
                "December 2020": {
                    "value": 62432.78,
                    "details": {
                        "Management fees received": 62432.78
                    }
                },
                "January 2021": {
                    "value": 62432.78,
                    "details": {
                        "Management fees received": 62432.78
                    }
                },
                "February 2021": {
                    "value": 62432.78,
                    "details": {
                        "Management fees received": 62432.78
                    }
                },
                "March 2021": {
                    "value": 62432.78,
                    "details": {
                        "Management fees received": 62432.78
                    }
                },
                "April 2021": {
                    "value": 62432.78,
                    "details": {
                        "Management fees received": 62432.78
                    }
                },
                "May 2021": {
                    "value": 62432.78,
                    "details": {
                        "Management fees received": 62432.78
                    }
                },
                "June 2021": {
                    "value": 62432.78,
                    "details": {
                        "Management fees received": 62432.78
                    }
                },
                "July 2021": {
                    "value": 62432.78,
                    "details": {
                        "Management fees received": 62432.78
                    }
                }
            }
        }
    }   
}

标签： sqljsonsql-server

解决方案

如果我对您的理解正确，并且您希望避免$."cost_to_client"对输入 JSON 部分进行显式列定义，则可以选择以下语句：

SELECT 
   j2.ACCID,
   h1.[key] AS [Month],
   h2.[Management fees received]
FROM OPENJSON(@WP_ACCOUNT, '$.result') j1
OUTER APPLY OPENJSON(j1.[value]) WITH (
   ACCID nvarchar(50) '$.management_account_id',
   cost_to_client nvarchar(max) '$.cost_to_client' AS JSON
) j2
CROSS APPLY OPENJSON(j2.cost_to_client) h1
CROSS APPLY OPENJSON(h1.[value]) WITH (
   [value] numeric(10, 2) '$.value',
   [Management fees received] numeric(10, 2) '$.details."Management fees received"'
) h2

结果：

ACCID Month         Management fees received
--------------------------------------------
12183 December 2020 62432.78
12183 January 2021  62432.78
12183 February 2021 62432.78
12183 March 2021    62432.78
12183 April 2021    62432.78
12183 May 2021      62432.78
12183 June 2021     62432.78
12183 July 2021     62432.78

推荐阅读