To homepage To homepage
Solved

Normalizing table containing filed in json format

  • 8 January 2020
  • 2 replies
  • 68 views
M
Rank

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?

icon

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

View original

2 replies

W
Rank
Badge

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

Christian Hauggaard
Rank
Userlevel 6
Badge +5

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:

 

 

Reply


Terms and ConditionsCookie settings