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

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

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


The code is below

SQL
CREATE PROCEDURE change_table(IN data JSON)
BEGIN
DECLARE ckeys VARCHAR(255);
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
Quote:
Query 1: Syntax error in JSON text in argument 1 to function 'json_extract' at position 2
Posted
Updated 4-Feb-21 6:18am
v3
Comments
Matthew Dennis 4-Feb-21 11:37am
   
try
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