Home How to convert JSON column values into separated schema with many rows in SQL Server?
Reply: 1

How to convert JSON column values into separated schema with many rows in SQL Server?

Farah Mussa
1#
Farah Mussa Published in 2017-12-07 19:46:44Z

I have a data set that has only one column has JSON format. Here is snap from the table:

Here are only Genres column in JSON that I want to create another table to have many rows for each row in this table to list the genre id and name and connect the two table by the primary key

I have used OPENJSON available in SQL Server but all what I get only for one record and not for the whole table

DECLARE @json nvarchar(MAX) = (SELECT TOP (1) [Genres]
  FROM [IMDB_movies].[dbo].[Genre_movies])

 SELECT * 
 FROM OPENJSON(@json)
 WITH (id FLOAT,
 name VARCHAR(100))

But I have no idea how to convert the whole Genres records in the table into new table of many rows for each tag in Genres record in the main table.

DineshDB
2#
DineshDB Reply to 2017-12-08 03:05:41Z

Try this answer:

DECLARE @Genre_movies TABLE(Generes VARCHAR(8000))
INSERT INTO @Genre_movies VALUES('[{"id":"28","name":"Action"},{"id":"12","name":"Adventure"}]')
INSERT INTO @Genre_movies VALUES('[{"id":"16","name":"Animation"},{"id":"14","name":"Fantasy"}]')

DECLARE @Result TABLE(ID INT,Name VARCHAR(20))

SELECT Generes,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) RN
INTO #Temp
FROM @Genre_movies

DECLARE @MIN INT,@MAX INT,@JSONValue VARCHAR(MAX)
SELECT @MAX=MAX(RN),@MIN=MIN(RN) FROM #Temp

WHILE(@MAX>=@MIN)
BEGIN
    SELECT @JSONValue=Generes FROM #Temp WHERE RN=@MIN

    INSERT INTO @Result
    SELECT * 
     FROM OPENJSON(@JSONValue)
     WITH (id FLOAT,
     name VARCHAR(100))

    SET @MIN=@MIN+1
END

SELECT * FROM @Result
DROP TABLE #Temp
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.390669 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO