Snowflake json_extract_path_text does not accept a key with spaces

Question

The data coming into the warehouse is structured like this

{"Client ID":"1234567","client_name":"Kareem" }

when I use the function

JSON_EXTRACT_PATH_TEXT(COLUMN_NAME, 'Client ID')

it give me this error :

Invalid extraction path 'Client ID': invalid token at position 7.

is there another workaround to get the value for this key ? or for using this key with the function to get the value out of the JSON column ?

Answer 1

Per its documentation, the JSON_EXTRACT_PATH_TEXT function follows the standard notation for object keys. Keys that carry spaces are required to be double-quoted, and the same applies here. The following works for your example:

JSON_EXTRACT_PATH_TEXT(COLUMN_NAME, '"Client ID"')

Answer 2

 COLUMN_NAME:"Client ID"

worked for me