How do I solve this stored procedure problem?

Question

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 4-Feb-21 5:30am

Member 15064692

Updated 4-Feb-21 6:18am

v3

Add a Solution

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

Add a Solution

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

Richard Deeming · Answer 1 · 2021-02-04T06:17:00

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.

How do I solve this stored procedure problem?

1 solution

Solution 1

Add your solution here

Preview 0

How do I solve this stored procedure problem?

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us