I need to return value of json expression

Question

My expression is

 {
    "author": "%{\"id\" => \"f63b6f8a-4e3e-4884-8ce8-21a7885da7a7\", \"name\" => \"Bye\", \"type\" => \"AUTOMATOR\"}"
  }

How do I return AUTOMATOR in the above express?

I am using JSON_EXTRACT(author,"$.type"). Tt is not returning anything

score 0 · Answer 1 · answered Dec 12 '20 at 12:38

0

Your json seems to be not valid.

Correct json will be:

 { "author": {"id" :"f63b6f8a-4e3e-4884-8ce8-21a7885da7a7", "name": "Bye", "type" : "AUTOMATOR"} }

you can validate JSONs online, for example at: https://jsonformatter.curiousconcept.com/#

answered Dec 12 '20 at 12:38

Nikita

How do I get the value of TYPE? – Manish Manohar Dec 12 '20 at 12:46
Can you please help me return the value of "type" in the correct json that you have shared? what expression should I use? – Manish Manohar Dec 12 '20 at 12:50
SELECT JSON_EXTRACT('{ "author": {"id" :"f63b6f8a-4e3e-4884-8ce8-21a7885da7a7", "name": "Bye", "type" : "AUTOMATOR"} }', '$.author.type'); – Nikita Dec 12 '20 at 12:52
I have checked the JSON from https://jsonformatter.curiousconcept.com/# JSON format seems to be correct. The solution that you have shared works on the expression shared by you but not with the original express. – Manish Manohar Dec 12 '20 at 13:04
the string { "author": "%{\"id\" => \"f63b6f8a-4e3e-4884-8ce8-21a7885da7a7\", \"name\" => \"Bye\", \"type\" => \"AUTOMATOR\"}" } is json but value of author is a only string not a valid json – Nikita Dec 12 '20 at 13:34
Okay. Is it possible to get the value of "TYPE" from the string? – Manish Manohar Dec 12 '20 at 13:47
with JSON instruments - no, you should write custom parser for it – Nikita Dec 12 '20 at 13:57
Cool. I think you are right. Can we convert that string in json and then get the value of "type"? – Manish Manohar Dec 12 '20 at 13:59
replace "=>" with ":" then I think you can JSON_EXTRACT(this_new_string,'$.type') – Nikita Dec 12 '20 at 14:01
I cannot replace => with : . I am parsing data in Google cloud. JSON_EXTRACT is giving me null.. – Manish Manohar Dec 12 '20 at 17:35

1 Answers1