Select certain keys from array of objects

Question

Say I have a JSON column foo where each value is an array of objects, i.e:

[{"date": somedate, "value": somevalue, "othervaluesidontneed":...},
{"date": somedate, "value": somevalue, "othervaluesidontneed":...},
{"date": somedate, "value": somevalue, "othervaluesidontneed":...},...]

I want to select this column but for each row, to only include the keys date and value, so the returned value is:

[{"date": somedate, "value": somevalue},
{"date": somedate, "value": somevalue},
{"date": somedate, "value": somevalue},...]

Is this possible?

GMB · Accepted Answer · 2019-10-28 10:25:23Z

2

A solution would be to use json_table() (available in MySQ 8.0 only) to expand the array as a set of rows, and then generate a new array of objects that contain only the requested keys with json_arrayagg():

select 
    json_arrayagg(json_object( 'date', tt.date, 'value', tt.value)) new_js
from 
    mytable t,
    json_table(
        js,
        "$[*]"
        columns(
            date datetime path "$.date",
            value int path "$.value"
        )
    ) as tt
group by t.id

This requires that some column can be used to uniquely identify a row in the initial table: I called that column id.

Demo on DB Fiddle:

with mytable as (
    select 1 id, '[
        { "date": "2019-01-01", "value": 1, "othervaluesidontneed": 12 },
        { "date": "2019-01-02", "value": 2, "othervaluesidontneed": 55 },
        { "date": "2019-01-03", "value": 3, "othervaluesidontneed": 72}
    ]' js
)
select 
    json_arrayagg(json_object( 'date', tt.date, 'value', tt.value)) new_js
from 
    mytable t,
    json_table(
        js,
        "$[*]"
        columns(
            date datetime path "$.date",
            value int path "$.value"
        )
    ) as tt
group by t.id

| new_js                                                                                                                                                       |
| :----------------------------------------------------------------------------------------------------------------------------------------------------------- |
| [{"date": "2019-01-01 00:00:00.000000", "value": 1}, {"date": "2019-01-02 00:00:00.000000", "value": 2}, {"date": "2019-01-03 00:00:00.000000", "value": 3}] |

edited Oct 28, 2019 at 10:25

answered Oct 28, 2019 at 10:24

GMB

224k25 gold badges103 silver badges151 bronze badges

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

4 Comments

Nick Over a year ago

JSON_TABLE is only available in MySQL 8+

GMB Over a year ago

@Nick: indeed. I just edited my answer to mention this. OP did not state which MySQL version they are using.

Nick Over a year ago

Agreed - so it just needed saying that it was an 8+ function since they may have 5.7 which does have limited JSON support. Anyway, nice answer.

cb7 Over a year ago

Thank you, I am stuck on 5.7 unfortunately. Although I appreciate the detailed answer and will accept your answer should no one else respond!

Collectives™ on Stack Overflow

Select certain keys from array of objects

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related