Search code examples
syntaxmariadbsql-function

Syntax Error #1064 when using MariaDB JSON_REPLACE function

I'm using MariaDB version 10.3.39. I have create a table named "sys_config" with multiple columns. A column name is "json_system". It's data type is assigned to "longtext". Using this reference: https://mariadb.com/kb/en/json_replace/

I have inserted { "A": 1, "B": [2, 3]} in this column.

When I query

SELECT json_system FROM sys_config

everything goes ok.

OK

But when I try to query:

SELECT JSON_REPLACE(SELECT json_system FROM sys_config, '$.B[1]', 4)

A Syntax Error will be occurred: Syntax Error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT json_system FROM sys_config, '$.B[1]', 4) LIMIT 0, 25' at line 1

Why this happen? According to MariaDB reference when I query: SELECT JSON_REPLACE('{ "A": 1, "B": [2, 3]}', '$.B[1]', 4); the query will be successful:

OK What's wrong with the second query? When I pass the "SELECT" result into the "JSON_REPLACE" function a syntax error will be appeared.

I have tried below query: SELECT JSON_REPLACE('{ "A": 1, "B": [2, 3]}', '$.B[1]', 4);

This works.

But the query:

SELECT JSON_REPLACE(SELECT json_system FROM sys_config, '$.B[1]', 4)

doesn't work.

Solution

  • When using a SELECT query as an expression, it must be enclosed in an [extra] pair of parens:

    SELECT JSON_REPLACE((SELECT json_system FROM ...))
                    ^                           ^