我使用的是SQL Server,其中有一列将json存储为字符串.我正在try 将json分解/规范化为新的行和列.下面是表格当前外观的图片.每个PricePointID在价格json列中至少有一条记录.每个JSON数组在每一行中都有相同的键.将 for each 价格对象创建新的行,然后每个键将成为其自己的列.
一个PricePointID的例子,它在价格json数组中有多个价格对象,应该 for each 价格id分成多行.
# PricePointId
40844
# Prices
[{"id":252820,"component_id":106965,"starting_quantity":1,"ending_quantity":1,"unit_price":"20.0","price_point_id":40844,"formatted_unit_price":"$20.00","segment_id":null},{"id":595550,"component_id":106965,"starting_quantity":2,"ending_quantity":5,"unit_price":"10.0","price_point_id":40844,"formatted_unit_price":"$10.00","segment_id":null},{"id":595551,"component_id":106965,"starting_quantity":6,"ending_quantity":null,"unit_price":"5.0","price_point_id":40844,"formatted_unit_price":"$5.00","segment_id":null}]
Desired results should look like this mock up in excel. Each object for the associated PricePointId has been given it's own row and the keys have been turned into columns.
Desired Results
我try 了OPENJSON和STRING_SPLIT,但似乎两者都不起作用.