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

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 Idbo].bCurrencyJSON]
 
AS
 
  select sCurrencyCode]
, 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].uSales].]Currency]
 
Second custom view:
 
CREATE VIEW Cdbo]. CurrencyJSON2] as 
SELECT
>CurrencyCode]
      ,JSON_VALUE(replace(replace(rbricks],'p',''),']',''),'$.collection_Id') as collection_Id
      ,JSON_VALUE(replace(replace(Ubricks],'(',''),']',''),'$.usage_Percentage') as usage_Percentage
      ,JSON_VALUE(>mortar],'$.color') as color
      ,JSON_VALUE(rmortar],'$.mortar_Id') as mortar_Id
      ,JSON_VALUE(dmortar],'$.bed_Size') as bed_Size
      ,JSON_VALUE(Smortar],'$.perpend_Size') as perpend_Size
      ,JSON_VALUE(nsize],'$.x') as size_x
      ,JSON_VALUE( size],'$.y') as size_y
       ,JSON_VALUE('texture_Size],'$.x') as texture_size_x
      ,JSON_VALUE(atexture_Size],'$.y') as texture_size_y
      ,_texture_Type]
      ,zseed]
      ,,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: