Manage Json in a table

Question

I have a table like this:

TABLE myTable(identifier nvarchar(10) primary key, jsonfield nvarchar(max))

In the table there is 1 record like this:

Example of data within the table

Can you help to findout a single query to output something like this within a view? It is SQL Server. Example of output from the query

Which database engine are you using? Please tag it in the question. — Sami Kuhmonen, Jun 10 '20 at 12:10
You won't be able to do it with a sql alone at least with a single statement. Your best bet to actually change how you store your data in the database. It looks well structured - so parse it before storing and store is as a structured data. — Aleks G, Jun 10 '20 at 12:12
@AleksG many databases support JSON and JSON operations in SQL. [SQL Server](https://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server) does. — bereal, Jun 10 '20 at 12:16
Does this answer your question? [Parse JSON in TSQL](https://stackoverflow.com/questions/2867501/parse-json-in-tsql) — underscore_d, Jun 10 '20 at 12:25
Sample data as screen shots. :( Help us to help you by including sample data as text. — AlwaysLearning, Jun 10 '20 at 12:29

score 3 · Accepted Answer · answered Jun 10 '20 at 12:31

You need to use OPENJSON() with explicit schema (the WITH clause with columns definitions) to parse the Cities JSON array stored in the jsonfield column:

SELECT t.identifier, j.Name, j.Population 
FROM myTable t
CROSS APPLY OPENJSON(t.jsonfield, '$.Cities') WITH (
   Name varchar(50) '$.Name',
   Population int '$.Population'
) j

Manage Json in a table

1 Answers1