How to correctly use group_concat with json_array() in mySQL ver 8.0.28-cll-lve

Question

Scenario

I wish to SELECT from a table with simple varchar and integer columns and return a JSON representation of the whole table. This is so that later I can use PhP's curl() to send the 'table' to another server and reconstruct it. I'm using MySQL ver 8.0.28-cll-lve on a remote, shared, server hosted at my ISP. I do not have admin access to the server itself, just all access rights to use and maintain the database.

What I have tried

The code below and in this SQLfiddle in response to this post on Stack Exchange's 'Database Administrators' plus several other posts on SO on the exactly the same topic (I won't list them all as I expect the first reply will tell me this question duplicates them)

Sample code that should work

DROP TABLE IF EXISTS contact;
CREATE TABLE contact
(
     name_field VARCHAR  (5) NOT NULL,
  address_field VARCHAR (20) NOT NULL,
  contact_age   INTEGER      NOT NULL
);

INSERT INTO contact
VALUES
('Mary', 'address one',   25),
('Fred', 'address two',   35),
('Bill', 'address three', 47);

SELECT
CONCAT
('[', REPLACE
  (
    REPLACE
    (
      GROUP_CONCAT
      (
        JSON_ARRAY
        (
          'name_field:', name_field, 
          'address_field:', address_field, 
          'age_field:', contact_age
        ) SEPARATOR ','
      ), '[', '{'
    ), ']', '}'
  ), ']'
) 
AS best_result2 
FROM contact

Result of running this code in SQL fiddle under MySQL 8.0

[{"name_field:", "Mary", "address_field:", "address one", "age_field:", 25},{"name_field:", "Fred", "address_field:", "address two", "age_field:", 35},{"name_field:", "Bill", "address_field:", "address three", "age_field:", 47}]

Problem

When I run this code in SQL fiddle using mySQL ver 8.0 it produces the correct result above. However if I copy/paste the code from SQLfiddle into SQLyog 12.4.3 and run it against a MySQL database version 8.0.28-cll-lve I get the following syntax error

Error Code: 1064

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SEPARATOR ','

The code looks OK to me. Can anyone see a reason for this please?

Edit - more strangeness

Thanks to forpas but I couldn't see any difference between his code that worked and mine that gave a syntax error As an experiment I put all my code on one line - still got the error Then I removed all the spaces apart from after SELECT, AS and FROM and it worked OK . I can only assume that having spaces in the wrong place was spoiling the syntax, although that seems unlikely.

Edit 2 - Solved, reason for the syntax error

Thanks to everyone who pointed out that the code I was using did not produce valid JSON. That is not really the point, it produced something, not a syntax error, so is slightly irrelevant to my question. The code came from an accepted answer on Database Administrators which, as it worked in SDQLfiddle without syntax errors, I assumed would work in my DBMS (I didn't notice the slight issue with the JSON at the time but I could easily fix that later).

However user forpas came up with the correct answer to my question on why I was getting a syntax error, which is the copy/paste action put a space between GROUP_CONCAT and its following bracket. Remove that space and the code runs without syntax errors.

*Result of running this code in SQL fiddle under MySQL 8.0* This is not valid JSON. — Akina, Mar 05 '22 at 15:56
That comment should really be directed at user Vérace in Database Administrators as it his code, not mine. But you are right, the colon after the key should be outside the quoted string. That's to do with parameters passed to the JSON_ARRAY() function - any idea how it should be done? However, the code at least produces a string output, not a syntax error, so my question still is valid (unless the JSON_ARRAY() parameters are what is causing the problem, in which case what is the solution? — user2834566, Mar 05 '22 at 16:10
The problematic part of the code is the space after GROUP_CONCAT and before `(`. If you remove it everything works fine: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=220c7a1f89165bf343e2fd39a4add51e — forpas, Mar 05 '22 at 16:55

Akina · Answer 1 · 2022-03-05T18:03:15.820

Result of running this code in SQL fiddle under MySQL 8.0

[{"name_field:", "Mary", "address_field:", "address one", "age_field:", 25},{"name_field:", "Fred", "address_field:", "address two", "age_field:", 35},{"name_field:", "Bill", "address_field:", "address three", "age_field:", 47}]

This is not valid JSON. This is some JSON-like string.

Test does this is safe for you:

SELECT JSON_ARRAYAGG(JSON_OBJECT('name_field', name_field,
                                 'address_field', address_field,
                                 'contact_age', contact_age)) all_contacts
FROM contact;

The code which gives provided desired output precisely:

SELECT CONCAT('[',
         GROUP_CONCAT(
           CONCAT(
               '{',
               CONCAT_WS(', ', 
                   '"name_field:"', JSON_QUOTE(name_field), 
                   '"address_field:"', JSON_QUOTE(address_field), 
                   '"age_field:"', contact_age
                   ),
               '}'
              )
            ),
         ']'    
         ) one_value
FROM contact

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=568f2f8f0ef7e6dfb10941b7469fa0db

Thank you, your code works correctly and is of about the same complexity.. It's useful to have an alternative method. However your answer does not really answer my question as to why I was getting a syntax error, just gives another way to achieve the result I want. Accordingly, although it is a very good answer that I am sure will be of use to others, I feel I have to marlk the answer by forpas as correct as they answered what was actually asked. — user2834566, Mar 05 '22 at 17:32
@user2834566 The code which gives provided output precisely added. — Akina, Mar 05 '22 at 18:03

score 1 · Accepted Answer · answered Mar 05 '22 at 17:18

Your problem is actually documented in Function Name Parsing and Resolution/Built-In Function Name Parsing:

The parser uses default rules for parsing names of built-in functions. These rules can be changed by enabling the IGNORE_SPACE SQL mode.

When the parser encounters a word that is the name of a built-in function, it must determine whether the name signifies a function call or is instead a nonexpression reference to an identifier such as a table or column name....

some built-in functions have special parsing or implementation considerations, so the parser uses the following rules by default to distinguish whether their names are being used as function calls or as identifiers in nonexpression context:

To use the name as a function call in an expression, there must be no whitespace between the name and the following ( parenthesis character.

Conversely, to use the function name as an identifier, it must not be followed immediately by a parenthesis

The function GROUP_CONCAT() belongs to the list of functions which are affected by the IGNORE_SPACE setting.

So, remove any spacing between GROUP_CONCAT and (.

See the demo.

Thanks forpas. I am aware of that rule and when I write my own code I wouldn't put a space between a function name and the bracket. I just didn't notice it in the code I pasted from SQLfiddle - guess I've been staring at too much JSON and PHP recently, so was quite happy to have everything on different lines! — user2834566, Mar 05 '22 at 17:25
@user2834566 my coding style is to have the `(` right after the name of the function in the same row and the arguments of the function in separate rows (if there are more than 1 or 2) so I have never experienced such a problem. — forpas, Mar 05 '22 at 17:30
That's a good idea. I often use SQLinForm to format my SQL for me so I can adapt that to include that layout — user2834566, Mar 05 '22 at 17:35

How to correctly use group_concat with json_array() in mySQL ver 8.0.28-cll-lve

2 Answers2