时间戳

首页 > 解决方案 > 如何创建包含 JSON 的列，该列的名称从另一个表中的列的值定义？

sql - 如何创建包含 JSON 的列，该列的名称从另一个表中的列的值定义？

问题描述

我有一个源表，其中包含 VARCHAR 格式的数据，如下例所示。我想以 JSON 格式将数据插入到另一个表中（结果列本身可以是 JSON 或 VARCHAR 类型）。

SourceTable:
____________________________
| Id | JSONName | JSONValue |
|____|__________|___________|
| 1  | Name     | John      |
| 2  | Name     | Henry     |
| 2  | Age      | 32        |
| 3  | Age      | 56        |
| 3  | Location | US        |
| 4  | Age      | 24        |
| 4  | Name     | Andrew    |
| 4  | Location |           |

我想要的是：

Expected ResultTable:
____________________________________________________
| Id |               ResultJSON                     |
|____|______________________________________________|
| 1  | {"Name":"John"}                              |
| 2  | {"Name":"Henry","Age":"32"}                  |
| 3  | {"Age":"56", "Location":"US"}                |
| 4  | {"Age":"24","Name":"Andrew","Location":null} |

我当前的查询得到了什么：

Wrong resultTable:
_______________________________________________________________________________________________________________________________
| Id |               ResultJSON                                                                                                |
|____|_________________________________________________________________________________________________________________________|
| 1  | [{"JSONName":"Name","JSONValue":"John"}]                                                                                |
| 2  | [{"JSONName":"Name","JSONValue":"Henry"},{"JSONName":"Age","JSONValue":"32"}]                                           |
| 3  | [{"JSONName":"Age","JSONValue":"56"},{"JSONName":"Location","JSONValue":"US"}]                                          |
| 4  | [{"JSONName":"Age","JSONValue":"24"},{"JSONName":"Name","JSONValue":"Andrew"},{"JSONName":"Location","JSONValue":null}] |

当前查询：

INSERT INTO ResultTable
(
     Id
    ,ResultJSON
)
SELECT
     SourceTable.Id
    ,JSON_AGG(SourceTable.JSONName,SourceTable.JSONValue)
FROM SourceTable
INNER JOIN OtherTable ON SourceTable.Id=OtherTable.Id

是否可以使用 Teradata JSON 函数来做到这一点？如果不是，那么最优化的查询是什么？

标签： sqljsonteradata

解决方案

您可以使用 RegEx 删除不需要的部分：

SELECT
   SourceTable.Id
  ,RegExp_Replace(Cast(Json_Agg(SourceTable.JSONName AS "#A",SourceTable.JSONValue AS "#B") AS VARCHAR(32000)), '"#A":|,"#B"|^\[|\]$|}(?=,{")|(?<="},){')
FROM SourceTable
GROUP BY 1

The RegEx removes all of the follwing:

  • "#A":
  • ,"#B"
  • a leading [
  • a trailing ]
  • } if it's followed by ,{"
  • { if it's following "},

Edit:

Based on the comments this RegEx leaves superfluous opening braces. This seems to work better:

'"#A":|,"#B"|^[|]$|}(?=,)|(?<=,){'

推荐阅读