Normalizing table containing filed in json format

  • 8 January 2020
  • 2 replies


I have a question for you all:

I have a table for example link this


Product_id: Integer

Delivery_metod: varchar(50)

Inventory_id: Integer

Inventory_Entries: JSON

Inventory_entries can contain one or several entries in JSON format like this:


Instead off one line with the json entries nested into one single field i would like to Normalize the tabel into several lines, one for each SKU.

Any ideas?


Best answer by Christian Hauggaard 17 January 2023, 10:01

View original

2 replies


If your field Inventory_Entries has a maximum number of entries per Inventory_id, I would suggest:

1. CROSS APPLY or UNPIVOT the Inventory_Entries field into a temp table or a custom table

2. LEFT JOIN that new temp /custom table to your original table and store the output of that join in another custom table

Since I can't tell what the PK is of your table, I can only suggest that it's best to unpivot or cross apply round your PK and perform your join on that. I assumed here that it's Inventory_id.

Kind Regards,


Userlevel 5
Badge +5


Apologies for the late response. I recently had another question regarding this. For example, the below data was provided in JSON format

The data is now like this:

{"bricks":[{"collection_Id":1171,"usage_Percentage":1.0}],"mortar":{"color":null,"mortar_Id":10,"bed_Size":0.01,"perpend_Size":0.01},"size":{"x":3.0,"y":3.0},"texture_Size":{"x":4096,"y":4096},"texture_Type":["diffuse texture","albedo texture","normal texture","alpha texture","ao texture"],"seed":808554121,"tileable":true,"result_Type":"file"}


And the desired output was the following columns:






I was able to return the desired output by creating two custom views using the JSON functions: JSON_VALUE() and JSON_QUERY(). See documentation below
First custom view:
CREATE VIEW [dbo].[CurrencyJSON]
  select [CurrencyCode]
, JSON_QUERY([JSONField],'$.bricks') as bricks
, JSON_QUERY([JSONField],'$.mortar') as mortar
, JSON_QUERY([JSONField],'$.size') as size
, JSON_QUERY([JSONField],'$.texture_Size') as texture_Size
, JSON_QUERY([JSONField],'$.texture_Type') as texture_Type
, JSON_value([JSONField],'$.seed') as seed
, JSON_value([JSONField],'$.tileable') as tileable
, JSON_value([JSONField],'$.result_Type') as result_Type
   FROM [AdventureWorks].[Sales].[Currency]
Second custom view:
CREATE VIEW [dbo].[CurrencyJSON2] as 
      ,JSON_VALUE(replace(replace([bricks],'[',''),']',''),'$.collection_Id') as collection_Id
      ,JSON_VALUE(replace(replace([bricks],'[',''),']',''),'$.usage_Percentage') as usage_Percentage
      ,JSON_VALUE([mortar],'$.color') as color
      ,JSON_VALUE([mortar],'$.mortar_Id') as mortar_Id
      ,JSON_VALUE([mortar],'$.bed_Size') as bed_Size
      ,JSON_VALUE([mortar],'$.perpend_Size') as perpend_Size
      ,JSON_VALUE([size],'$.x') as size_x
      ,JSON_VALUE([size],'$.y') as size_y
       ,JSON_VALUE([texture_Size],'$.x') as texture_size_x
      ,JSON_VALUE([texture_Size],'$.y') as texture_size_y
  FROM [AdventureWorks].[dbo].[CurrencyJSON]

I then created a table, added the fields and then added a table insert from my second view

A preview of the data

You should also be able to do the same with custom field transformations instead of views. For example: