Get data from json array mysql into cells

Question

So I get such an array coming in my procedure

{"4":false,"5":true,"6":false,"sch":"28"}

Previously I used JSON_UNQUOTE(JSON_EXTRACT( DATAA, '$.sch')) to get the sch value from an array and put it in a cell, but now the task has changed and I need to get such records in the tables after the INSERT procedure

MARKS_ID |  SCHEDULE_ID | STUDENT_ID | MARKS_BOOL | MARKS_DATE
-------: | -----------: | ---------: | ---------: | --------:
       1 |           28 |          4 |      false |  CURTIME()
       2 |           28 |          5 |       true |  CURTIME()
       3 |           28 |          6 |      false |  CURTIME()

MARKS_ID, SCHEDULE_ID, STUDENT_ID = INT
MARKS_BOOL = BOOLEAN
MARKS_DATE = DATETIME

GMB · Accepted Answer · 2020-05-10 13:54:38Z

1

If you are running MySQL 8.0, you can use json_table() for this. The idea is to extract the keys of the object using json_keys(), and to unnest them as rows using json_table(). Then, you can filter out unwanted key sch, and extract each corresponding value in the select clause:

set @js = '{"4":false,"5":true,"6":false,"sch":"28"}';

select 
    marks_id,
    json_unquote(json_extract(@js, '$.sch')) schedule_id,
    0 + student_id student_id,
    json_unquote(json_extract(@js, concat('$."', student_id, '"'))) = 'true' marks_bool
from json_table(
    json_keys(@js), 
    '$[*]' 
    columns (
        marks_id for ordinality,
        student_id varchar(10) path '$'
    )
) x
where student_id <> 'sch'

Demo on DB Fiddle:

marks_id | schedule_id | student_id | marks_bool
-------: | ----------: | ---------: | ---------:
       1 |          28 |          4 |          0
       2 |          28 |          5 |          1
       3 |          28 |          6 |          0

You can easily turn this to an insert query:

insert into mytable(marks_id, schedule_id, student_id, marks_bool, marks_date)
select 
    marks_id,
    json_unquote(json_extract(@js, '$.sch')),
    student_id,
    json_unquote(json_extract(@js, concat('$."', student_id, '"'))) = 'true',
    now()
from json_table(
    json_keys(@js), 
    '$[*]' 
    columns (
        marks_id for ordinality,
        student_id varchar(10) path '$'
    )
) x
where student_id <> 'sch'

Demo

edited May 10, 2020 at 13:54

answered May 9, 2020 at 23:06

GMB

224k25 gold badges103 silver badges151 bronze badges

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

3 Comments

Serhiy Osmolenko Over a year ago

But I need to insert data from the array to a table. Not select.

Serhiy Osmolenko Over a year ago

Do you specify schedule_id manually? I've tried to use JSON_EXTRACT( @js, '$.sch'); but then i don't get any data on select =( You can check this out. DB Fiddle

GMB Over a year ago

@SerhiyOsmolenko: there are a few errors in your fiddle, fixed. But you are right, I misunderstood that part of your requirement. I modified my answer accordingly (there is no no need to use a temporary variable).

Collectives™ on Stack Overflow

Get data from json array mysql into cells

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related