How to access JSON field with DOT in the key

ekrish · January 28, 2022, 2:46am

Hello All, Is there a way to retrieve a JSON field that has period in the key name.
For ex Table Tab has JSON field Amount and the content is as below

{“tot.Amt”:1000,“tot.BalAm”:200,“tot.PymtAm”:0}

How do I retrieve the fields?

Select Tab.Amount::$tot.Amt from Tab; returns error.

rob · February 7, 2022, 7:37pm

You can accomplish this by surrounding the key name with backtics (or quotes when in sql_mode ANSI_QUOTES):

Select Tab.Amount::$`tot.Amt` from Tab;