Skip to content

Add SELECT FOR JSON AUTO support in Babelfish (#2243)#2270

Merged
forestkeeper merged 1 commit intobabelfish-for-postgresql:BABEL_3_X_DEVfrom
amazon-aurora:jira-babel-3668-15x
Jan 19, 2024
Merged

Add SELECT FOR JSON AUTO support in Babelfish (#2243)#2270
forestkeeper merged 1 commit intobabelfish-for-postgresql:BABEL_3_X_DEVfrom
amazon-aurora:jira-babel-3668-15x

Conversation

@Jakeowen1
Copy link
Copy Markdown
Contributor

@Jakeowen1 Jakeowen1 commented Jan 19, 2024

Description

This change adds SELECT FOR JSON AUTO support to Babelfish which nests JSON objects based on the structure of the Select statement.

Issues Resolved

BABEL-3668

Test Scenarios Covered

  • Use case based -
select U.Id AS "users.userid", O.productId AS "order.productId", O.Id AS "product.oid", P.price AS "product.price" FROM forjson_nesting_vu_t_users U JOIN forjson_nesting_vu_t_orders O ON (U.id = O.userid) JOIN forjson_nesting_vu_t_products P ON (P.id = O.productid) FOR JSON AUTO

[{"users.userid": 1, "o": [{"order.productId": 1, "product.oid": 2, "p": [{"product.price": "20"}, {"product.price": "20"}, {"product.price": "30"}, {"product.price": "30"}]}, {"order.productId": 1, "product.oid": 1, "p": [{"product.price": "20"}, {"product.price": "20"}, {"product.price": "30"}, {"product.price": "30"}]}]}]
  • Boundary conditions -
select U.Id AS "users.userid", O.productId AS "order.productId", O.Id AS "product.oid", P.price AS "product.price", S.totalSales AS "totalsales" FROM forjson_nesting_vu_t_users U JOIN forjson_nesting_vu_t_orders O ON (U.id = O.userid) JOIN forjson_nesting_vu_t_products P ON (P.id = O.productid) JOIN forjson_nesting_vu_t_sales S ON (P.price = S.price) FOR JSON AUTO

[{"users.userid": 1, "o": [{"order.productId": 1, "product.oid": 2, "p": [{"product.price": "20", "s": [{"totalsales": 50}, {"totalsales": 50}]}]}, {"order.productId": 1, "product.oid": 1, "p": [{"product.price": "30", "s": [{"totalsales": 100}, {"totalsales": 100}]}]}, {"order.productId": 1, "product.oid": 2, "p": [{"product.price": "30", "s": [{"totalsales": 100}, {"totalsales": 100}]}]}, {"order.productId": 1, "product.oid": 1, "p": [{"product.price": "20", "s": [{"totalsales": 50}, {"totalsales": 50}]}]}]}]
  • Arbitrary inputs -
INSERT INTO forjson_nesting_vu_t_sales VALUES (1, NULL, NULL), (2, NULL, NULL);
GO

select U.Id AS "users.userid", O.productId AS "order.productId", O.Id AS "product.oid", P.price AS "product.price", S.totalSales AS "totalsales" FROM forjson_nesting_vu_t_users U JOIN forjson_nesting_vu_t_orders O ON (U.id = O.userid) JOIN forjson_nesting_vu_t_products P ON (P.id = O.productid) JOIN forjson_nesting_vu_t_sales S ON (P.price = S.price) FOR JSON AUTO

[{"users.userid": 1, "o": [{"order.productId": 1, "product.oid": 2, "p": [{"product.price": "20", "s": [{"totalsales": 50}, {"totalsales": 50}]}]}, {"order.productId": 1, "product.oid": 1, "p": [{"product.price": "30", "s": [{"totalsales": 100}, {"totalsales": 100}]}]}, {"order.productId": 1, "product.oid": 2, "p": [{"product.price": "30", "s": [{"totalsales": 100}, {"totalsales": 100}]}]}, {"order.productId": 1, "product.oid": 1, "p": [{"product.price": "20", "s": [{"totalsales": 50}, {"totalsales": 50}]}]}]}]
  • Negative test cases -
1> select cast(null as datetime) for JSON AUTO
2> go
json                                                                                                                                                                                                                                                            
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[{}]                                                                                                                                                                                                                                                            

(1 rows affected)
1>

  • Minor version upgrade tests -
    NA

  • Major version upgrade tests -
    Added to upgrade scripts

  • Performance tests -
    NA

  • Tooling impact -
    NA

  • Client tests -
    NA

Check List

  • Commits are signed per the DCO using --signoff

By submitting this pull request, I confirm that my contribution is under the terms of the Apache 2.0 and PostgreSQL licenses, and grant any person obtaining a copy of the contribution permission to relicense all or a portion of my contribution to the PostgreSQL License solely to contribute all or a portion of my contribution to the PostgreSQL open source project.

For more information on following Developer Certificate of Origin and signing off your commits, please check here.

@Jakeowen1
Add SELECT FOR JSON AUTO support in Babelfish (babelfish-for-postgres…
fee02d1 
…ql#2243)

This change adds SELECT FOR JSON AUTO support to Babelfish which nests JSON objects based on the structure of the Select statement.

Task: BABEL-3668
Signed-off-by: Jake Owen <owjco@amazon.com>
@coveralls
Copy link
Copy Markdown
Collaborator

coveralls commented Jan 19, 2024

Pull Request Test Coverage Report for Build 7586511364

  • 0 of 0 changed or added relevant lines in 0 files are covered.
  • No unchanged relevant lines lost coverage.
  • Overall coverage increased (+0.1%) to 72.438%
Totals Coverage Status
Change from base Build 7581088206: 0.1%
Covered Lines: 40113
Relevant Lines: 55376
💛 - Coveralls

@forestkeeper forestkeeper merged commit 675ecc1 into babelfish-for-postgresql:BABEL_3_X_DEV Jan 19, 2024
staticlibs pushed a commit to wiltondb/babelfish_extensions that referenced this pull request Apr 22, 2024
@Jakeowen1 @staticlibs
Add SELECT FOR JSON AUTO support in Babelfish (babelfish-for-postgres…
77c02b1 
…ql#2243) (babelfish-for-postgresql#2270)

This change adds SELECT FOR JSON AUTO support to Babelfish which nests JSON objects based on the structure of the Select statement.

Task: BABEL-3668
Signed-off-by: Jake Owen <owjco@amazon.com>
@shardgupta shardgupta deleted the jira-babel-3668-15x branch January 8, 2026 08:05
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@forestkeeper forestkeeper forestkeeper approved these changes

Assignees

No one assigned

Labels

None yet

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

3 participants

@Jakeowen1 @coveralls @forestkeeper