时间戳

首页 > 解决方案 > SQL JSON 嵌套选择

sql - SQL JSON 嵌套选择

问题描述

我试图通过不两次生成相同的信息来提高查询的性能。

我想嵌套一些 JSON 而不在列名下添加 JSON。

我要嵌套的 JSON 存储在另一列中，因此我的查询如下所示：

SELECT
        CONCAT(p.branch, p.policyref) AS 'policy_id'
      , p.insurer                     AS 'insurer_code'
      , p.Brand                       AS 'policy_name'
      , p.sale_date                   AS 'sale_date'
      , p.start_date                  AS 'start_date'
      , p.end_date                    AS 'end_date'
      , p.status
      , p.customer_data_json -- This is the column with the other nested JSON
    FROM
        dbo.ic_brpolicy p1
    WHERE
        p.policyref = p1.PolRef@
        AND p.branch = p1.B@
    FOR JSON PATH

“customer_data_json”列是我希望嵌套的 JSON，但是由于列名，它会将 JSON 嵌套在“customer_data_json”下，但是我只想嵌套数据而不创建列名。

即这个

"start_date": "2020-06-20T00:00:00Z",
"end_date": "2021-06-20T00:00:00Z",
"status": "ACTIVE",
"drivers": [{
    "driver_id": "Prop",
    "main_driver": true,
    "app_user": true,

代替

"start_date": "2020-02-27T00:00:00Z",
"end_date": "2021-02-27T00:00:00Z",
"status": "ACTIVE",
"customer_data_json": [{
    "drivers": [{
        "driver_id": "Prop",
        "main_driver": true,
        "app_user": true,

正如您所看到的，因为 JSON 来自“customer_data_json”列，它将信息嵌套在“customer_data_json”元素下，我不希望它开始“驱动程序”部分。

干杯

标签： sqljsonsql-server

解决方案

正确的语句取决于存储在customer_data_json列中的 JSON 的结构，但以下简化示例演示了一种可能的解决方案：

SELECT
   p.start_date AS start_date,
   p.end_date AS end_date,
   p.status AS status,
   JSON_QUERY(p.customer_data_json, '$[0].drivers') AS drivers
FROM
   (VALUES ('2020-02-27T00:00:00', '2021-02-27T00:00:00', 'ACTIVE', '[{"drivers": [{"driver_id": "Prop","main_driver": true,"app_user": true}]}]')
) p (start_date, end_date, status, customer_data_json)
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

结果：

{
  "start_date":"2020-02-27T00:00:00",
  "end_date":"2021-02-27T00:00:00",
  "status":"ACTIVE",
  "drivers":[
    {
      "driver_id":"Prop",
      "main_driver":true,
      "app_user":true
    }
  ]
}

推荐阅读