SQL Server Explode/normalize a column with JSON stored as a string into rows and columns [duplicate]

wf82jlnq 于 2023-04-10 发布在其他

关注(0)|答案(1)|浏览(145)

This question already has an answer here:

parsing JSON column in SQL Server (1 answer)
Closed 20 hours ago.

I am using SQL Server and have a column that has JSON stored as a string in it. I am trying to explode/normalize the JSON into new rows and columns. Below is a picture of how the table currently looks. Each PricePointId has at least one record in the Prices JSON column. Each JSON array has the same keys in each row. New rows would be made for each Prices object and then each key would be its own column.

View of the table.

An example of a PricePointId that has multiple price objects in the Prices JSON array that should be split into multiple lines for each Price 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

I have tried looking into OPENJSON and STRING_SPLIT but can't seem to get either to work.

sql-server

来源：https://stackoverflow.com/questions/75960078/explode-normalize-a-column-with-json-stored-as-a-string-into-rows-and-columns

1条答案

按热度按时间

llycmphe1#

Just to expand on my comment

Select A.[PricePointId] 
      ,B.*
 From  YourTable A
 Cross Apply ( Select *
                 From OpenJSON(Prices)
                 with ( component_id         int
                       ,starting_quantity    int
                       ,ending_quantity      int
                       ,unit_price           money
                       ,price_point_id       int
                       ,formatted_unit_price varchar(50)
                       ,segment_id           varchar(50)
                      )
             ) B

Results

赞(0）回复(0）举报 2023-04-10

我来回答

SQL Server Explode/normalize a column with JSON stored as a string into rows and columns [duplicate]

1条答案

相关问题

热门标签

最新问答