Skip to content

Improve support of JSON_OBJECT in MariaDB and MySQL compatibility modes #3579

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Merged
katzyn merged 1 commit into from
Jul 25, 2022
Merged

Improve support of JSON_OBJECT in MariaDB and MySQL compatibility modes #3579

katzyn merged 1 commit into from
Jul 25, 2022

Conversation

@bazoocaze
Copy link
Contributor

@bazoocaze bazoocaze commented Jul 24, 2022 •
edited
Loading

This change accepts the non-standard JSON_OBJECT syntax of MariaDB and MySQL in compatibility modes.

This will work:

SET MODE=MariaDB;
SELECT JSON_OBJECT('key1', 10, 'key2', 'str');

>> {"key1":10,"key2":"str"}

Resolves #3575

@bazoocaze bazoocaze mentioned this pull request Jul 24, 2022
Closed
katzyn
katzyn reviewed Jul 24, 2022
View reviewed changes
Copy link
Contributor

@katzyn katzyn left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Thank you for your contribution!

Please, take a look on review comments and send a license statement as described here
https://h2database.com/html/build.html#providing_patches
to our mailing list:
https://groups.google.com/g/h2-database
(it is partially pre-moderated, some new messages may become visible only after approvement)

@bazoocaze
Copy link
Contributor Author

bazoocaze commented Jul 24, 2022

PR: I will review and fix the PR as for the comments. Thanks.

The guys at MariaDB accepted the claim on the colon ':' separator syntax 🎉
Could you please point where in the ISO SQL standard is the syntax for KEY/VALUE keywords as found on H2?

you and H2 guys are right, the standard syntax is with a colon ':', not with a comma ','. I agree that we should support the standard syntax. As for key/value variant — I wasn't able to find it in the standard. Can you point out where the 2016 standard says that? Part, section, subsection, etc?

@bazoocaze bazoocaze force-pushed the fix-mysql-json-object branch from 52a85f8 to a413db5 Compare July 24, 2022 20:35
@katzyn
Copy link
Contributor

katzyn commented Jul 25, 2022

ISO/IEC 9075-2:2016 with technical corrigendum 2 defines both JSON_OBJECT and JSON_OBJECTAGG with the following definition for these parameters:

<JSON name and value> ::=
    <JSON name and value 1>
  | <JSON name and value 2>

<JSON name and value 1> ::=
  [ KEY ] <JSON name> VALUE <JSON value expression>

<JSON name and value 2> ::=
  <JSON name> <colon> <JSON value expression>

You can buy this part of standard here:
https://www.iso.org/standard/63556.html

These definition are from technical corrigendum 2, you can find it here (it looks like you can preview it without buying the main sources):
https://www.iso.org/obp/ui/#iso:std:iso-iec:9075:-2:ed-5:v1:cor:2:v1:en
See section 6.33, <JSON value constructor>.

For complete definitions see 9075-2:2016 or possibly 19075-6:2021. <JSON name and value> is modified in TC 2, but you can still use the same three possible ways as in 9075-2:2016:

KEY name VALUE valueExpression
name VALUE valueExpression
name : valueExpression

@katzyn katzyn merged commit 091cbe2 into h2database:master Jul 25, 2022
@ed0906 ed0906 mentioned this pull request Nov 29, 2022
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@katzyn katzyn katzyn left review comments

Assignees

No one assigned

Labels

None yet

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

Possible syntax mismatch for json_object in MySQL compatibility mode

2 participants

@bazoocaze @katzyn