如何在AWS RDS Babelfish（Aurora PostgreSQL）通过SSMS实现FOR JSON PATH功能

Prerequisites #

• Verify your Aurora PostgreSQL Babelfish cluster runs Babelfish version 3.0.0 or newer (check with SELECT babelfish_version();).
• If you need to use PostgreSQL native functions, adjust the escape hatch setting (if not already configured):

  ALTER SYSTEM SET babelfishpg_tsql.escape_hatch = 'moderate';
  -- Restart the cluster to apply changes
  

Step 1: Create the Employee Table (Same as SQL Server) #

Run this T-SQL in SSMS connected to your Babelfish cluster:

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')

Step 2: Query to Generate JSON with Null Values (Matching Original Output) #

Babelfish's native FOR JSON PATH doesn't support include_null_values yet, so use PostgreSQL's JSON functions to replicate the behavior. These functions automatically include null fields by default, matching your SQL Server output:

Direct Approach (No Intermediate Array) #

SELECT ',' AS [key], pg_catalog.json_build_object('id', id, 'name', name, 'state', state) AS [value]
FROM dbo.Employee WITH(nolock)

Mirrored Original Approach (Build Array First) #

If you want to replicate the original logic of creating a JSON array then splitting it into rows:

DECLARE @json1 NVARCHAR(Max) 
SET @json1 = (
    SELECT pg_catalog.json_agg(pg_catalog.json_build_object('id', id, 'name', name, 'state', state))
    FROM dbo.Employee WITH(nolock)
)
SELECT ',' AS [key], [value] 
FROM OPENJSON(@json1)

Key Details #

• json_build_object: Creates a JSON object from key-value pairs, retaining null fields (equivalent to include_null_values).
• json_agg: Aggregates individual JSON objects into a single array (matches FOR JSON PATH output).
• OPENJSON: Parses the JSON array into individual rows, just like in SQL Server.

内容的提问来源于stack exchange，提问作者swapna s