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

Question

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

Thanks in Advance!!!

Answer 1

SELECT FOR JSON PATH is supported in Babelfish v.2.3.0 (=latest current release).

Answer 2

Currently, JSON functionality isn't supported. See:

https://babelfishpg.org/docs/usage/limitations-of-babelfish

Cell on the left:

JSON

Cell on the right:

Datatypes, Built-in Functions, and statements are unsupported.

John Russell
Aurora PostgreSQL developer advocate