Read any Json data into a table structure using Dynamic SQL and save time write long queries.

Let's say you have a MYSQL database that stores form data as a JSON string. To read the data and manipulate it as SQL, you can use SQL link services. Here's how it works:

First, you create a temporary table to store the data from MYSQL. You can use the following SQL script to do this:

sql
/****** Object:  Table [dbo].[tbljson]    Script Date: 29/03/2023 10:37:48 AM ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[#tbljson](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[form_id] [int] NOT NULL,
	[params] [nvarchar](max) NULL,

 CONSTRAINT [PK_tbljson] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Once you've created the temporary table, you can insert the values from the MYSQL database into it using the following SQL script:

insert into #tbljson
	select * from openquery(CONAN,'SELECT form_id,params FROM homejm4.tbl_convertforms_conversions where form_id=2;')

Note that the openquery statement uses link services to connect to MYSQL.

Next, you can construct a table that can be manipulated as SQL by dynamically generating the table fields based on the JSON structure. This has the advantage of automatically updating the table fields if the JSON structure changes.

To dynamically generate the table fields, you can use the following SQL script:

DECLARE @TableName NVARCHAR(100) = 'tbljson'
DECLARE @Columns NVARCHAR(MAX) = ''

SELECT @Columns = @Columns + 'JSON_VALUE(Params, ''$."' + [key] + '"'' ) AS ' + QUOTENAME([key]) + ',' 
FROM (
    SELECT DISTINCT [key] FROM #tbljson
    CROSS APPLY OPENJSON(Params)
) AS Keys

DECLARE @Query NVARCHAR(MAX) = 'SELECT ' + LEFT(@Columns, LEN(@Columns) - 1) + ' FROM ' + @TableName

PRINT @Query -- optionally print the query to check before executing

EXEC(@Query)

The script uses OPENJSON to parse the JSON string and extract the keys. It then constructs a dynamic SQL query that selects the values of each key as separate columns. Finally, the script prints the SQL query (optional) and executes it to construct the table that can be manipulated as SQL.