Syntax Error #1064 when using MariaDB JSON_REPLACE function

Question

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.

But when I try to query:

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

A Syntax Error will be occurred:

#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:

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.

the query make no sense at all, the inner select will return a lot of rows, but json_repace needs there a json not a bunch of rows — nbk
– nbk, Commented Jan 12, 2024 at 21:15
@nbk the table only has one row. It does not make a difference whether the database table has one row or several rows. The syntax is incorrect. — Scott Jennings
– Scott Jennings, Commented Jan 13, 2024 at 6:16

Rick James · Accepted Answer · 2024-01-12 23:28:21Z

-1

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 ...))
                    ^                           ^

answered Jan 12, 2024 at 23:28

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Scott Jennings Over a year ago

Thanks. This query SELECT JSON_REPLACE((SELECT json_system FROM sys_config), '$.B[1]', 4) worked for me. What I noticed is that if I use SELECT JSON_REPLACE(SELECT json_system FROM sys_config, '$.B[1]', 4) then the MariaDB will considers sys_config, as an extension of the expression, not the second argument of JSON_REPLACE.

Collectives™ on Stack Overflow

Syntax Error #1064 when using MariaDB JSON_REPLACE function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related