CODEWITHSUNDEEP
mysqlsqlarraysjsonselect
cb7
cb7

Reputation: 523

Select certain keys from array of objects

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?

Upvotes: 1

Views: 215

Answers (1)

GMB
GMB

Reputation: 222702

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}] |

Upvotes: 2

Related Questions