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

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

user820
1#
user820 Published in April 25, 2018, 12:13 pm

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.

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO