To remove double quotes from date string in SQL

Question

I am using JSON_EXTRACT() to retrieve the date from json.

I want to get the date without double quotes.

Here is the example of what I am doing :

JSON_EXTRACT(JSON_EXTRACT(events, "$.my_member"), "$.my_Number") as xyz

my_number holds date string as "2016-01-01 11:31:25", I want this without the double quotes.

I tried using timestamp as :

timestamp(JSON_EXTRACT(JSON_EXTRACT(events, "$.my_member"), "$.my_Number"))

but it is returning a null value to xyz.

Thanks.

Answer 1

Try

JSON_EXTRACT_SCALAR(JSON_EXTRACT(events, "$.my_member"), "$.my_Number")

Also, you should be able to further "optimize" your expression by building proper JSON Path and using JSON function only ones. See "hint" below

SELECT 
  JSON_EXTRACT_SCALAR(
    '{"my_member":{"my_Number":"2016-01-01 11:31:25"}}', 
    "$.my_member.my_Number"
  )  

See more details and also difference between JSON_EXTRACT_SCALAR and JSON_EXTRACT at JSON functions

Answer 2

Run REPLACE

REPLACE(JSON_EXTRACT(JSON_EXTRACT(events, "$.my_member"), "$.my_Number"),"\"","") as xyz