Click here to Skip to main content
15,032,967 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a stored procedure that i call with a JSON object of paired values. I can't get it to output to a table what am i doing wrong?

The table should output as a table with two columns, key and value. An example input would be

CALL change_table('{foo:true, dan:3, 44:bill}');

Which would output this
| key         | value                  |
|  foo        | true                   |
|  dan        | 3                      |
|  44         | bill                   |

The code is below

CREATE PROCEDURE change_table(IN data JSON)
DECLARE cvalue VARCHAR(255);

SET ckeys = json_extract(data, '$.`key`');
SET cvalue = json_extract(data, '$.`value`');

INSERT INTO table_1(`key`, `value`)
VALUES (ckeys, cvalue);

END //

What I have tried:

I have Managed to get the function to exist in the database with table plus but there is a new problem. I now get this error
Query 1: Syntax error in JSON text in argument 1 to function 'json_extract' at position 2
Updated 4-Feb-21 6:18am
Matthew Dennis 4-Feb-21 11:37am
CALL change_table('{foo:"true", dan:"3", 44:"bill"}');
Member 15064692 4-Feb-21 11:40am
That part isn't the problem it just keeps saying there is a syntax error. If i use mariadb it says that the error is on line three at '' which doesn't help.

1 solution

Using the Jayway JsonPath evaluator[^], it seems that $.`key` will not match anything in your input document.

From a quick read of the JSONPath syntax[^], it seems that this is looking for a property called key on your JSON object. There is no such property.

The same problem applies to $.`value` - the is no property called value on the object.

I suspect you will need to use JSON_KEYS[^] to extract the keys, and $.* to extract the values. However, I don't have access to a MariaDB instance to test this. Based on the documentation, I suspect you will still end up with a single row in your result table.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900