CODEWITHSUNDEEP
mysqlsqljson
Bisoux
Bisoux

Reputation: 522

Extract date & time from JSON data in mysql

I have the following data,

{"value": 0, "action": "UPDATED", "effectiveFrom": "2019-01-31T07:27:13.000Z"}

I am trying to extract the time value using the below SQL,

SELECT CASE
         WHEN JSON_EXTRACT(changeData, '$.action') = 'UPDATED' THEN
          time(replace(JSON_EXTRACT(changeData, '$.effectiveFrom'), 'T', ' '))
         ELSE
          'N'
       END AS ACTION
  FROM change

I used the replace function having read here that for mysql the 'T' should be replaced. My sql is returning null.

Upvotes: 1

Views: 1753

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65343

You also need to replace double-quote and Z characters with '' or ' ' while replacing T with ' ' character for the time literal as

SELECT JSON_EXTRACT(changeData, '$.value') AS value,
       CASE
        WHEN JSON_EXTRACT(changeData, '$.action') = 'UPDATED' 
        THEN 
            TIME(
            REPLACE(REPLACE(REPLACE(
                                    JSON_EXTRACT(changeData, '$.effectiveFrom')
            ,'"',''),"Z",''),"T",' ')
            )
       ELSE 'N'
      END AS action
  FROM `change`

Demo

Upvotes: 1

Related Questions