[MDEV-26506] Over-quoted JSON when combining JSON_ARRAYAGG with JSON_OBJECT - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.5
Fix Version/s: 10.6.3
Component/s: JSON
Labels:
None

Description

Run the following query on a fresh installation of MariaDB 10.6.x:

openxs@ao756:~/dbs/maria10.6$ bin/mysql --socket=/tmp/mariadb.sock

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 6

Server version: 10.6.5-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select json_object('attr2', ifnull(u.o, json_array())) j from

mysql.user u1 left join (select user, json_arrayagg(json_object('attr1', user))

as o from mysql.user group by user) u on u1.user = u.user where u1.user = 'root';

+--------------------------------------+

| j                                    |

+--------------------------------------+

| {"attr2": "[{\"attr1\": \"root\"}]"} |

+--------------------------------------+

1 row in set (0.007 sec)

It will return a single row for the "root" user, but note that attributes and values in the array have backslash before the ". While with MySQL:

openxs@ao756:~/dbs/maria10.6$ bin/mysql -uroot --socket=/tmp/maysql8.sock

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MySQL connection id is 11

Server version: 8.0.26 Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> select json_object('attr2', ifnull(u.o, json_array())) j from mysql.user u1 left join (select user, json_arrayagg(json_object('attr1', user)) as o from mysql.user group by user) u on u1.user = u.user where u1.user = 'root';

+--------------------------------+

| j                              |

+--------------------------------+

| {"attr2": [{"attr1": "root"}]} |

+--------------------------------+

1 row in set (0.002 sec)

we have expected quoting for the values in the array.

Why is this difference, that I consider a bug in MariaDB?

Attachments

Issue Links

is blocked by

MDEV-27018 IF and COALESCE lose "json" property

Closed

relates to

MDEV-13701 Nesting JSON

Closed

MDEV-16620 Add support for JSON_ARRAYAGG and JSON_OBJECTAGG

Closed

MDEV-21902 Nested JSON_ARRAYAGG in JSON_OBJECT should not get escaped

Closed

MDEV-27018 IF and COALESCE lose "json" property

Closed

MDEV-27666 User variable not parsed as geometry variable in geometry function

Closed

(1 relates to)

Activity

Ascending order - Click to sort in descending order

Valerii Kravchuk created issue - 2021-08-30 12:28

Alice Sherepa made changes - 2021-08-30 13:08

Field	Original Value	New Value
Link		This issue relates to ~~MDEV-21902~~ [ ~~MDEV-21902~~ ]

Alice Sherepa made changes - 2021-08-30 13:08

Link

This issue relates to ~~MDEV-16620~~ [ ~~MDEV-16620~~ ]

Alice Sherepa made changes - 2021-08-30 13:10

Affects Version/s		10.5 [ 23123 ]
Affects Version/s		10.6 [ 24028 ]

Alice Sherepa made changes - 2021-08-30 13:11

Fix Version/s		10.5 [ 23123 ]
Fix Version/s		10.6 [ 24028 ]

Alice Sherepa made changes - 2021-08-30 13:11

Assignee

Alexey Botchkov [ holyfoot ]

Alice Sherepa made changes - 2021-08-30 13:11

Status

Open [ 1 ]

Confirmed [ 10101 ]

Alice Sherepa added a comment - 2021-08-30 13:23 - edited

MariaDB [test]> select json_object("a",dt.x) from (select json_arrayagg(json_object('a1', 'name')) as x) dt ;

+-------------------------------+

| json_object("a",dt.x)         |

+-------------------------------+

| {"a": "[{\"a1\": \"name\"}]"} |

+-------------------------------+

1 row in set (0.006 sec)

MariaDB [test]> select json_object("a",json_arrayagg(json_object('a1', 'name')))  ;

+-----------------------------------------------------------+

| json_object("a",json_arrayagg(json_object('a1', 'name'))) |

+-----------------------------------------------------------+

| {"a": [{"a1": "name"}]}                                   |

+-----------------------------------------------------------+

1 row in set (0.001 sec)

I guess this is the same as ~~MDEV-13701~~.
test from comments there:

MariaDB [test]> SELECT JSON_OBJECT("main", JSON_ARRAY("one", 2));

+-------------------------------------------+

| JSON_OBJECT("main", JSON_ARRAY("one", 2)) |

+-------------------------------------------+

| {"main": ["one", 2]}                      |

+-------------------------------------------+

1 row in set (0.001 sec)

MariaDB [test]> SET @sub = JSON_ARRAY("one", 2);

Query OK, 0 rows affected (0.001 sec)

MariaDB [test]> SELECT JSON_OBJECT("main", @sub);

+---------------------------+

| JSON_OBJECT("main", @sub) |

+---------------------------+

| {"main": "[\"one\", 2]"}  |

+---------------------------+

1 row in set (0.001 sec)

Alice Sherepa added a comment - 2021-08-30 13:23 - edited MariaDB [test]> select json_object("a",dt.x) from (select json_arrayagg(json_object('a1', 'name')) as x) dt ; +-------------------------------+ | json_object("a",dt.x) | +-------------------------------+ | {"a": "[{\"a1\": \"name\"}]"} | +-------------------------------+ 1 row in set (0.006 sec) MariaDB [test]> select json_object("a",json_arrayagg(json_object('a1', 'name'))) ; +-----------------------------------------------------------+ | json_object("a",json_arrayagg(json_object('a1', 'name'))) | +-----------------------------------------------------------+ | {"a": [{"a1": "name"}]} | +-----------------------------------------------------------+ 1 row in set (0.001 sec) I guess this is the same as MDEV-13701 . test from comments there: MariaDB [test]> SELECT JSON_OBJECT("main", JSON_ARRAY("one", 2)); +-------------------------------------------+ | JSON_OBJECT("main", JSON_ARRAY("one", 2)) | +-------------------------------------------+ | {"main": ["one", 2]} | +-------------------------------------------+ 1 row in set (0.001 sec) MariaDB [test]> SET @sub = JSON_ARRAY("one", 2); Query OK, 0 rows affected (0.001 sec) MariaDB [test]> SELECT JSON_OBJECT("main", @sub); +---------------------------+ | JSON_OBJECT("main", @sub) | +---------------------------+ | {"main": "[\"one\", 2]"} | +---------------------------+ 1 row in set (0.001 sec)

Alice Sherepa made changes - 2021-08-30 13:41

Link

This issue relates to ~~MDEV-13701~~ [ ~~MDEV-13701~~ ]

Sergei Golubchik made changes - 2021-11-10 23:07

Assignee

Alexey Botchkov [ holyfoot ]

Alexander Barkov [ bar ]

Sergei Golubchik made changes - 2021-12-06 21:36

Workflow

MariaDB v3 [ 124658 ]

MariaDB v4 [ 144404 ]

Alexander Barkov made changes - 2021-12-30 09:31

Link

This issue is blocked by ~~MDEV-27018~~ [ ~~MDEV-27018~~ ]

Alexander Barkov added a comment - 2021-12-30 10:05

Also repeatable with this script:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a VARCHAR(30));

INSERT INTO t1 VALUES ('root');

SELECT JSON_OBJECT('attr2',o) FROM (SELECT a, json_arrayagg(json_object('attr1', a)) as o FROM t1) u;

+--------------------------------------+

| JSON_OBJECT('attr2',o)               |

+--------------------------------------+

| {"attr2": "[{\"attr1\": \"root\"}]"} |

+--------------------------------------+

Alexander Barkov added a comment - 2021-12-30 10:05 Also repeatable with this script: DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a VARCHAR (30)); INSERT INTO t1 VALUES ( 'root' ); SELECT JSON_OBJECT( 'attr2' ,o) FROM ( SELECT a, json_arrayagg(json_object( 'attr1' , a)) as o FROM t1) u; +--------------------------------------+ | JSON_OBJECT('attr2',o) | +--------------------------------------+ | {"attr2": "[{\"attr1\": \"root\"}]"} | +--------------------------------------+

Anders Karlsson added a comment - 2022-05-13 15:22 - edited

There is a workaround for most of these cases, which is to do a JSON_MERGE() with the offending JSON value that gets treated like a string. So

MariaDB> SELECT JSON_OBJECT('foo', (SELECT JSON_ARRAYAGG(JSON_OBJECT('bar', 1))));

+-------------------------------------------------------------------+

| JSON_OBJECT('foo', (SELECT JSON_ARRAYAGG(JSON_OBJECT('bar', 1)))) |

+-------------------------------------------------------------------+

| {"foo": "[{\"bar\": 1}]"}                                         |

+-------------------------------------------------------------------+

Can be fixed like this:

MariaDB> SELECT JSON_OBJECT('foo', JSON_MERGE('[]', (SELECT JSON_ARRAYAGG(JSON_OBJECT('bar', 1)))));

+-------------------------------------------------------------------------------------+

| JSON_OBJECT('foo', JSON_MERGE('[]', (SELECT JSON_ARRAYAGG(JSON_OBJECT('bar', 1))))) |

+-------------------------------------------------------------------------------------+

| {"foo": [{"bar": 1}]}                                                               |

+-------------------------------------------------------------------------------------+

Anders Karlsson added a comment - 2022-05-13 15:22 - edited There is a workaround for most of these cases, which is to do a JSON_MERGE() with the offending JSON value that gets treated like a string. So MariaDB> SELECT JSON_OBJECT('foo', (SELECT JSON_ARRAYAGG(JSON_OBJECT('bar', 1)))); +-------------------------------------------------------------------+ | JSON_OBJECT('foo', (SELECT JSON_ARRAYAGG(JSON_OBJECT('bar', 1)))) | +-------------------------------------------------------------------+ | {"foo": "[{\"bar\": 1}]"} | +-------------------------------------------------------------------+ Can be fixed like this: MariaDB> SELECT JSON_OBJECT('foo', JSON_MERGE('[]', (SELECT JSON_ARRAYAGG(JSON_OBJECT('bar', 1))))); +-------------------------------------------------------------------------------------+ | JSON_OBJECT('foo', JSON_MERGE('[]', (SELECT JSON_ARRAYAGG(JSON_OBJECT('bar', 1))))) | +-------------------------------------------------------------------------------------+ | {"foo": [{"bar": 1}]} | +-------------------------------------------------------------------------------------+

Alexander Barkov made changes - 2022-06-07 07:12

Link

This issue relates to ~~MDEV-27018~~ [ ~~MDEV-27018~~ ]

Sergei Golubchik made changes - 2022-06-07 18:32

Assignee

Alexander Barkov [ bar ]

Rucha Deodhar [ rucha174 ]

Sergei Golubchik made changes - 2022-06-07 18:32

Fix Version/s		10.7 [ 24805 ]
Fix Version/s		10.8 [ 26121 ]

Alexander Barkov made changes - 2022-06-08 10:16

Link

This issue relates to ~~MDEV-27666~~ [ ~~MDEV-27666~~ ]

Josef Johansson added a comment - 2022-08-23 12:08

An interesting fact is that JSON_ARRAYAGG affects JSON_TYPE

CREATE TEMPORARY TABLE temp (id LONG, msg VARCHAR(5) );

INSERT INTO temp VALUES

(1, "abcde"),

(2, "bcdea"),

(3, "cdeab"),

(4, "deabc"),

(5, "eabcd");

WITH q1 AS (

		SELECT

id,

		  JSON_ARRAY(msg) AS q1a,

		  JSON_ARRAYAGG(msg) AS q1b,

		  JSON_TYPE(JSON_ARRAYAGG(msg)) AS q1c,

		  JSON_TYPE(JSON_ARRAY(msg)) AS q1d

		FROM temp

SELECT

  q1c AS s1c,

  JSON_OBJECTAGG(id, q1a) AS s1a,

  q1d AS s1d,

  JSON_OBJECTAGG(id, q1b) AS s1b

FROM q1 \G

WITH q2 AS (

		SELECT

id,

		  JSON_ARRAY(msg) AS q2a,

		  JSON_TYPE(JSON_ARRAY(msg)) AS q2b,

		  5 AS q2c,

		  JSON_TYPE(5) AS q2d

		FROM temp

SELECT

  q2b AS s2b,

  JSON_OBJECTAGG(id, q2a) AS s2a,

  JSON_TYPE(q2c) AS s2c,

  q2d AS s2d

FROM q2 \G

WITH q3 AS (

		SELECT

id,

		  JSON_ARRAYAGG(msg) AS q3a,

		  JSON_TYPE(JSON_ARRAYAGG(msg)) AS q3b,

		  5 AS q3c,

		  JSON_TYPE(5) AS q3d

		FROM temp

SELECT

  q3b AS s3b,

  JSON_OBJECTAGG(id, JSON_MERGE('[]',q3a)) AS s3a,

  JSON_TYPE(q3c) AS s3c,

  q3d AS s3d

FROM q3 \G

*************************** 1. row ***************************

s1c: ARRA

s1a: {"1":"[\"abcde\"]"}

s1d: ARRA

s1b: {"1":"[\"abcde\",\"bcdea\",\"cdeab\",\"deabc\",\"eabcd\"]"}

*************************** 1. row ***************************

s2b: ARRAY

s2a: {"1":["abcde"], "2":["bcdea"], "3":["cdeab"], "4":["deabc"], "5":["eabcd"]}

s2c: INTEGER

s2d: INTEGER

*************************** 1. row ***************************

s3b: ARRA

s3a: {"1":["abcde", "bcdea", "cdeab", "deabc", "eabcd"]}

s3c: INTEGER

s3d: INTE

Could it be that JSON_OBJECTAGG is not detecting it as an ARRAY?

Josef Johansson added a comment - 2022-08-23 12:08 An interesting fact is that JSON_ARRAYAGG affects JSON_TYPE CREATE TEMPORARY TABLE temp (id LONG, msg VARCHAR(5) ); INSERT INTO temp VALUES (1, "abcde"), (2, "bcdea"), (3, "cdeab"), (4, "deabc"), (5, "eabcd"); WITH q1 AS ( SELECT id, JSON_ARRAY(msg) AS q1a, JSON_ARRAYAGG(msg) AS q1b, JSON_TYPE(JSON_ARRAYAGG(msg)) AS q1c, JSON_TYPE(JSON_ARRAY(msg)) AS q1d FROM temp ) SELECT q1c AS s1c, JSON_OBJECTAGG(id, q1a) AS s1a, q1d AS s1d, JSON_OBJECTAGG(id, q1b) AS s1b FROM q1 \G WITH q2 AS ( SELECT id, JSON_ARRAY(msg) AS q2a, JSON_TYPE(JSON_ARRAY(msg)) AS q2b, 5 AS q2c, JSON_TYPE(5) AS q2d FROM temp ) SELECT q2b AS s2b, JSON_OBJECTAGG(id, q2a) AS s2a, JSON_TYPE(q2c) AS s2c, q2d AS s2d FROM q2 \G WITH q3 AS ( SELECT id, JSON_ARRAYAGG(msg) AS q3a, JSON_TYPE(JSON_ARRAYAGG(msg)) AS q3b, 5 AS q3c, JSON_TYPE(5) AS q3d FROM temp ) SELECT q3b AS s3b, JSON_OBJECTAGG(id, JSON_MERGE('[]',q3a)) AS s3a, JSON_TYPE(q3c) AS s3c, q3d AS s3d FROM q3 \G *************************** 1. row *************************** s1c: ARRA s1a: {"1":"[\"abcde\"]"} s1d: ARRA s1b: {"1":"[\"abcde\",\"bcdea\",\"cdeab\",\"deabc\",\"eabcd\"]"} *************************** 1. row *************************** s2b: ARRAY s2a: {"1":["abcde"], "2":["bcdea"], "3":["cdeab"], "4":["deabc"], "5":["eabcd"]} s2c: INTEGER s2d: INTEGER *************************** 1. row *************************** s3b: ARRA s3a: {"1":["abcde", "bcdea", "cdeab", "deabc", "eabcd"]} s3c: INTEGER s3d: INTE Could it be that JSON_OBJECTAGG is not detecting it as an ARRAY?

Sergei Golubchik made changes - 2023-01-16 22:33

Assignee

Rucha Deodhar [ rucha174 ]

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2023-01-16 22:33

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Sergei Golubchik made changes - 2023-01-16 22:34

Assignee	Sergei Golubchik [ serg ]	Alexander Barkov [ bar ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Sergei Golubchik made changes - 2023-01-16 22:34

Fix Version/s		10.9 [ 26905 ]
Fix Version/s		10.10 [ 27530 ]
Fix Version/s		10.11 [ 27614 ]

Sergei Golubchik added a comment - 2023-01-17 10:00

Already fixed in 10.6 in commit add782a13e5

Sergei Golubchik added a comment - 2023-01-17 10:00 Already fixed in 10.6 in commit add782a13e5

Sergei Golubchik made changes - 2023-01-17 10:00

Fix Version/s		N/A [ 14700 ]
Fix Version/s	10.5 [ 23123 ]
Fix Version/s	10.6 [ 24028 ]
Fix Version/s	10.7 [ 24805 ]
Fix Version/s	10.8 [ 26121 ]
Fix Version/s	10.9 [ 26905 ]
Fix Version/s	10.10 [ 27530 ]
Fix Version/s	10.11 [ 27614 ]
Resolution		Won't Fix [ 2 ]
Status	In Review [ 10002 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2023-01-17 10:00

Affects Version/s	10.6 [ 24028 ]
Affects Version/s	10.6.4 [ 26033 ]
Affects Version/s	10.6.5 [ 26034 ]

Sergei Golubchik made changes - 2023-01-17 10:01

Assignee	Alexander Barkov [ bar ]	Sergei Golubchik [ serg ]
Resolution	Won't Fix [ 2 ]
Status	Closed [ 6 ]	Stalled [ 10000 ]

Sergei Golubchik made changes - 2023-01-17 10:01

Fix Version/s		10.6.3 [ 25904 ]
Fix Version/s	N/A [ 14700 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Jira Automation (IT) made changes - 2024-07-04 02:11

Zendesk Related Tickets

187969

People

Assignee:: Sergei Golubchik

Reporter:: Valerii Kravchuk

Votes:: 0 Vote for this issue

Watchers:: 7 Start watching this issue

Dates

Created:: 2021-08-30 12:28

Updated:: 2024-07-07 21:01

Resolved:: 2023-01-17 10:01

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.