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

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.

CodePudding user response：

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.

CodePudding user response：

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;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=934730e199fa87a379e662653cf23ca1