CODEWITHSUNDEEP
mysqljson
Harsh Saudagar
Harsh Saudagar

Reputation: 478

How to filter a JSON datatype in mysql based on property name?

I have a JSON datatype column named 'months' in my mysql database.

This is a sample value of 'months' for a row: {"2018Apr": "2500", "2018Jun": "9000", "2018May": "4000", "enddate": "2018-06-14T18:30:00.000Z", "startdate": "2018-04-26T18:30:00.000Z"}.

All keys other than 'enddate' and 'startdate' can vary (i.e 'startdate' and 'enddate' will always exist in the JSON) for the 'months' column. I want to run a select query to get all the values from this column excluding the 'startdate' and 'enddate' keys and their values.

i.e. Expected output from the select query :- {"2500","9000", "4000"}

Unfortunately the best I could get was values from all the keys resulting in :- {"2500","9000", "4000", "2018-06-14T18:30:00.000Z", "2018-04-26T18:30:00.000Z"}

This is the query I used:- 

SELECT `months` -> '$.*' 
  from `Project` 
 where `pId` ="6d43c24f-b258-4b75-8524-26873c643748" 
   AND `creationTime` IN (SELECT MAX(`creationTime`) FROM `Project` GROUP BY `id`);

The MySql docs contain information to filter on the basis of the JSON value but not from keys.Any help will be greatly appreciated.

Upvotes: 1

Views: 446

Answers (1)

Nick
Nick

Reputation: 147216

You can remove the startdate and enddate keys from months using JSON_REMOVE, and then use JSON_EXTRACT to get the remaining values:

select json_extract(json_remove(months, '$.startdate', '$.enddate'), '$.*')
from `Project` 
where `pId` ="6d43c24f-b258-4b75-8524-26873c643748" 
AND `creationTime` IN (SELECT MAX(`creationTime`) FROM `Project` GROUP BY `id`);

Should give

["2500", "9000", "4000"]

Upvotes: 1

Related Questions