Incorrect parse between redshift JSON_EXTRACT_PATH_TEXT and databricks JSON path expression

[FlaviovLeal]

Hello,

So I tried the following code and got no errors. But the result SQL would not work on Databricks.

import sqlglot

sql = """select JSON_EXTRACT_PATH_TEXT('{ "farm": {"barn": { "color": "red", "feed stocked": true }}}', 'farm', 'barn', 'color');"""
print(sqlglot.transpile(sql, read='redshift', write='databricks')[0])
# The printed results are as follow:
#  SELECT JSON_EXTRACT_PATH_TEXT('{ "farm": {"barn": { "color": "red", "feed stocked": true }}}', 'farm', 'barn', 'color')

There are two possible solutions that would work on Databricks

select '{ "farm": {"barn": { "color": "red", "feed stocked": true }}}':['farm']['barn']['color'];

And

select '{ "farm": {"barn": { "color": "red", "feed stocked": true }}}':farm.barn.color;

I recommend the first solution, as the following would work

select '{ "farm": {"barn": { "color": "red", "feed stocked": true }}}':['farm']['barn']['feed stocked'];

but the following would not

select '{ "farm": {"barn": { "color": "red", "feed stocked": true }}}':farm.barn.feed stocked;

I tried looking into the code but I was unable to find a solution. On the databricks side, it looks like it would work (Transformer, Parser
), as long as no more than one keyword was used and there were no spaces on the keyword ('color' would work, but 'feed stocked' would not). But I'm unsure if it should use JSONExtract as its a child from the binary class and it can take multiple arguments.

Lastly, despite saying it's redshift. The same problem should happen on postgres, as this function exists there as well.

[georgesittas]

Planning to work on improving our parsing / generation logic for json paths this week, so this should be addressed soon.