Spliiting complex string column to multiple columns

1

hello
I got a movie database from kaggle
and the geners column looks like this
row 1:
[{"id": 28, "name": "Action"}, {"id": 12, "name": "Adventure"}, {"id": 14, "name": "Fantasy"}, {"id": 878, "name": "Science Fiction"}]
row 2:
[{"id": 12, "name": "Adventure"}, {"id": 14, "name": "Fantasy"}, {"id": 28, "name": "Action"}]
...
...
...

How can I make columns which only have Action, Fantasy , Adventure etc.... (seperately)

thank you very much

2

This is JSON.

Test data which you should have provided:

CREATE TABLE #t
(
	row_id int NOT NULL
	,Categories nvarchar(MAX) NOT NULL
);
INSERT INTO #t
VALUES (1, '[{"id": 28, "name": "Action"}, {"id": 12, "name": "Adventure"}, {"id": 14, "name": "Fantasy"}, {"id": 878, "name": "Science Fiction"}]')
	,(2, '[{"id": 12, "name": "Adventure"}, {"id": 14, "name": "Fantasy"}, {"id": 28, "name": "Action"}]');

The JSON can be expanded out with something like:

SELECT T.row_id, X.Category
FROM #t T
	CROSS APPLY OPENJSON(Categories) WITH ([Category] nvarchar(50) '$.name') X;

which can then be rolled up again with something like:

SELECT T.row_id, STRING_AGG(X.Category, ',') AS Categories
FROM #t T
	CROSS APPLY OPENJSON(Categories) WITH ([Category] nvarchar(50) '$.name') X
GROUP BY T.row_id;
2 Likes