Hi.
I have a question for you all:
I have a table for example link this
filed_name:Format
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:
[{"SKU":"A","Price":"100"},{"SKU":"B","Price":""00"}]
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
Hi
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:
bricks.collection_Id
bricks.usage_Percentage
Mortar.color
Mortar.mortar_id
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
https://docs.microsoft.com/en-us/sql/t-sql/functions/json-query-transact-sql?view=sql-server-ver15
First custom view:
CREATE VIEW [dbo].[CurrencyJSON]
AS
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
SELECT
[CurrencyCode]
,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
,[texture_Type]
,[seed]
,[tileable]
,[result_Type]
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:
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,
Wim
+5Hi
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:
bricks.collection_Id
bricks.usage_Percentage
Mortar.color
Mortar.mortar_id
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
https://docs.microsoft.com/en-us/sql/t-sql/functions/json-query-transact-sql?view=sql-server-ver15
First custom view:
CREATE VIEW [dbo].[CurrencyJSON]
AS
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
SELECT
[CurrencyCode]
,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
,[texture_Type]
,[seed]
,[tileable]
,[result_Type]
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:
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
