Explode/normalize a column with JSON stored as a string into rows and columns

Question

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.

Answer 1

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