Can anyone help me on how to fetch MySQL table/s data in JSON format by querying itself which is similar to MS SQL Server

Below is the existing T-SQL code which generates table data in JSON format just by using the keyword FOR JSON PATH, include_null_value.

How to implement the same in MySQL query itself??? Need possible solutions.

/** Sample Table data in MS SQL server ***/

CREATE TABLE [dbo].[Employee](
                              [id]    INT,
                              [name]  VARCHAR(25),
                              [state] VARCHAR(25) 
                             )


INSERT INTO [dbo].[Employee] values
(1,'SwapnaS','Bangalore'),
(2,'Divya','Hyderabad'),
(3,'Akshaya','Delhi'),
(4,'Rajini',NULL)

/** Fetching table data in JSON format ***/

DECLARE @json1 NVARCHAR(Max) 
SET @json1 = (
              SELECT * 
                FROM dbo.Employee WITH(nolock)
                 FOR JSON PATH, include_null_values 
)
SELECT ',' AS [key],[value] 
  FROM OPENJSON(@json1)

/** MS SQL results to **/

key	value
,	{“id”:4,”name”:”Rajini”,”state”:null}
,	{“id”:1,”name”:”Swapna”,”state”:”Bangalore”}
,	{“id”:2,”name”:”Divya”,”state”:”Hyderabad”}
,	{“id”:3,”name”:”Akshaya”,”state”:”Delhi”}

Thanks in Advance!!!

Answer

You can use JSON_OBJECT() function such as

SELECT JSON_OBJECT(
                   'id',id , 
                   'name',name,
                   'state',state
                   ) AS value
  FROM Employee

Demo