How to loop through JSON data in MySQL

Question

I want to use a counter to loop through JSON data in mysql, but I keep getting this error:

        "code": "ER_INVALID_JSON_PATH",
        "errno": 3143,
        "sqlMessage": "Invalid JSON path expression. The error is around character position 2.",
        "sqlState": "42000",
        "index": 0,

Sample table from created_slots. It is a JSON Array that contains times This is the response i got from Postman:

"slots": "[\"11:00:00.000000\", \"12:00:00.000000\", \"12:30:00.000000\", \"13:00:00.000000\", \"13:30:00.000000\", \"14:00:00.000000\", \"14:30:00.000000\", \"15:00:00.000000\", \"15:30:00.000000\", \"16:00:00.000000\", \"16:30:00.000000\", \"17:00:00.000000\", \"17:30:00.000000\"]"

Note: created_slots is a JSON Array

It appears counter is not a valid reference. Is there a way to parse it in the string. This is my code below:

      IF c_time > start_time THEN 
            removal_loop : LOOP
                IF JSON_EXTRACT(created_slots, '$[loop_counter]') <> 0 THEN
                    
                    IF JSON_EXTRACT(created_slots, '$[loop_counter]') < c_time THEN 
                        SET created_slots = JSON_REMOVE(created_slots, '$[loop_counter]');
                        SET loop_counter = loop_counter + 1;
                    ELSE
                        SET loop_counter = loop_counter + 1;
                    END IF;
                ELSE
                    LEAVE removal_loop;
                END IF;
            END LOOP removal_loop;
        END IF;

Alternative solution:

 IF JSON_EXTRACT(m_begin, '$[*]') < c_time THEN
     SET created_slots = JSON_REMOVE(m_begin, '$[refernece to current item]')
 ... remove here, but I can't use this line of code because everything 
  will be deleted. I need a reference to the item that is less than 
  c_time

please shows us also an example for created_slots. the path which is wrong i n your case is basicallly text, so you can concat varables to it, but for that your json has to be correctly — nbk
– nbk, Commented Oct 6, 2020 at 23:12
Please do not SHOUT when posting here. Text in ALL CAPS is more difficult to read and understand, and it won't get you help any faster. It's also rather impolite to come here and SHOUT at us when you're asking for free help. Thanks. — Ken White
– Ken White, Commented Oct 6, 2020 at 23:16
@nbk Please see the edited post. My Bad forgot to turn my caps off when I got out of mysql workbench — idiot
– idiot, Commented Oct 6, 2020 at 23:21
that is no valid json, please see if you can create a dbfiddle with one row — nbk
– nbk, Commented Oct 6, 2020 at 23:26
@nbk Sorry, it's the response I got from postman when I tested the api. The code above is a modification to a function the API calls. Please see the alternative solution I suggested. It'll be simpler if it can be achieved. — idiot
– idiot, Commented Oct 6, 2020 at 23:32

nbk · Accepted Answer · 2020-10-06 23:50:27Z

1

As i can only have a guess at what your json looks like, but you should always create a sample because we need [mre]

If the json is correct you need following path

Schema (MySQL v8.0)

CREATE TABLE t1 (created_slots JSON);


INSERT INTO t1 VALUES('{ "slots": ["11:00:00.000000", "12:00:00.000000", "12:30:00.000000", "13:00:00.000000", "13:30:00.000000", "14:00:00.000000", "14:30:00.000000", "15:00:00.000000", "15:30:00.000000", "16:00:00.000000", "16:30:00.000000", "17:00:00.000000", "17:30:00.000000"]}');

Query #1

SELECT 
JSON_EXTRACT(created_slots, '$.slots[0]')
FROM t1;

| JSON_EXTRACT(created_slots, '$.slots[0]') |
| ----------------------------------------- |
| "11:00:00.000000"                         |

View on DB Fiddle

answered Oct 6, 2020 at 23:50

nbk

49.6k8 gold badges32 silver badges53 bronze badges

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

Collectives™ on Stack Overflow

How to loop through JSON data in MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related