How to use SQL Server's 'Select FOR JSON PATH' in AWS RDS Babelfish DB (Aurora PostgreSQL) using SSMS

Viewed 64

Below 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 Babelfish (Aurora PostgreSQL enabling Bablefish cluster) query by connecting to SSMS 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,'Divya',NULL),
(2,'Akshay','Bengaluru'),
(3,'Kavya','Kolkata')

/** 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 **/

SQLOutput

Thanks in Advance!!!

1 Answers
Related