Thank you for your contribution and interest in JSQLParser.
This is heavy stuff and I do need a few minutes to digest it please.

I am sorry, I am old school and lost on JSON:

In my understanding, this should not be even parsed as a Colum, but as a Key Value Pair:

"i"."json_data":"rocket_name"

At the same time a Column with an Alias is nothing else but a Value Key pair.
So I wonder if it would not make more sense to define a new entity KeyValuePair or JSONColumn for this purpose.

Honestly, this is a tough one:

1. your solution and implementation is robust and deals with the problem fast

2. also you did not cause it, because someone else messed up column already (could even have been me)

3. I do understand that JSON is an important part these days

4. but I feel like a Database Column should be a Database Column and a MAP/JSON Key Value Pair should be differentiated. At the same time I can't tell you any clear benefit since Column is a Value Key Pair and so is the JSon Value Key pair.

What's the way forward? Do we want to take the "hack" right now, document it with a lot @Todo: separate Key-Value pairs from Columns, watch what happens in real life and take care of it later?

Idea!

Why don't we parse ("i"."json_data":"rocket_name")::VARCHAR as a Column with an Alias, only that Alias has ":" instead of "AS". In my opinion this would be the most flexible and correct solution here. Alias would get a type property with variants like COLUMN_ALIAS and MAP_KEY.

What do you think?

Hi @manticore-projects, thanks for the speedy responses! 😁

this should not be even parsed as a Colum, but as a Key Value Pair

Not sure I understand sorry - what would the key be and what would the value be? Note that the bit of the expression that references the json can be any length e.g. mytable.mycolumn:grandparent.parent.child....

So I wonder if it would not make more sense to define a new entity KeyValuePair or JSONColumn for this purpose.

The problem is, we can't actually know if it's a Json expression or not just by looking at the colon, because I think certain databases (e.g. informix? - the one in #1134) use colons for all delimiters - like normal column expressions. So how would we know to parse it into a JsonColumn or a normal Column?

Why don't we parse ("i"."json_data":"rocket_name")::VARCHAR as a Column with an Alias, only that Alias has ":" instead of "AS".

Not sure I understand sorry 🤔 so we'd have a new field on Column, which is an Alias? And the Alias would contain ":rocket_name"? If so, I feel like that's less accurate/a little more confusing than what we currently have.

Not sure I understand sorry - what would the key be and what would the value be? Note that the bit of the expression that references the json can be any length e.g. mytable.mycolumn:grandparent.parent.child....

I guess it is me, who does not understand it correctly.
What does mytable.mycolumn:grandparent.parent.child... mean regarding the part grandparent.parent.child?

I came from the perspective that Map entries like value:key are similar to columns, but your example has obviously a different meaning.

The problem is, we can't actually know if it's a Json expression or not just by looking at the colon, because I think certain databases (e.g. informix? - the one in #1134) use colons for all delimiters - like normal column expressions. So how would we know to parse it into a JsonColumn or a normal Column?

Very strong argument, I totally get your point.

I think we can just go with your improvements and see what will happen.

No problem at all, sorry, I probably should have given some example json to illustrate it...

What does mytable.mycolumn:grandparent.parent.child... mean regarding the part grandparent.parent.child?

So say for example you load some json into the mycolumn column of the mytable table. You load 2 rows like:

// row 1
{
  "rocket": {
    "engine": {
      "power": "loads of power"
    }
  }
}
// row 2
{
  "rocket": {
    "engine": {
      "power": "even more power"
    }
  }
}

If you execute

SELECT mycolumn:rocket.engine.power AS rocket_power FROM mytable;

It'll return 2 rows like:

rocket_power
---------------
loads of power
even more power

No problem at all, sorry, I probably should have given some example json to illustrate it...

What does mytable.mycolumn:grandparent.parent.child... mean regarding the part grandparent.parent.child?

So say for example you load some json into the mycolumn column of the mytable table. You load 2 rows like:

// row 1
{
  "rocket": {
    "engine": {
      "power": "loads of power"
    }
  }
}
// row 2
{
  "rocket": {
    "engine": {
      "power": "even more power"
    }
  }
}

If you execute

SELECT mycolumn:rocket.engine.power AS rocket_power FROM mytable;

It'll return 2 rows like:

rocket_power
---------------
loads of power
even more power

Thank you very much for the explanation, very helpful. I have been too stuck in my current maps and key/value pairs.
So basically we are talking more about a path into the content of a column, but not a key.

I think you implementation is good and fixes the problem.
Ready to merge when you correct the Spotless complaints. Run './gradlew :spotlessApply' to fix these violations.

Thank you again for your contribution and time, well done!

No worries at all 😁

So basically we are talking more about a path into the content of a column, but not a key.

Yeah exactly - on Snowflake, the bit after the colon is similar to a jsonpath expression 👍

Ready to merge when you correct the Spotless complaints. Run './gradlew :spotlessApply' to fix these violations.

OK great! I've got to head off now but will sort it out first thing tomorrow. Thanks again for your lightning fast review! 😁

Good job @mountaincrab and I would merge immediately when the Q/A test ran though.

Thanks again for the review @manticore-projects 👍

Months later I stumble on it while working on JSON expressions.
This whole thing is wrong because : is not a COLUMN delimeter but a JSON EXTRACT PATH operator:

-- DataBricks
SELECT c1:price j
    FROM VALUES('{ "price": 5 }') AS T(c1);

-- regular SQL
SELECT c1::JSON -> 'price' j
    FROM VALUES('{ "price": 5 }') AS T(c1);

So I am going to rewrite this part, which may break whatever you have been using it for.

Months later I stumble on it while working on JSON expressions. This whole thing is wrong because : is not a COLUMN delimeter but a JSON EXTRACT PATH operator:

-- DataBricks
SELECT c1:price j
    FROM VALUES('{ "price": 5 }') AS T(c1);

-- regular SQL
SELECT c1::JSON -> 'price' j
    FROM VALUES('{ "price": 5 }') AS T(c1);

So I am going to rewrite this part, which may break whatever you have been using it for.

Hi @manticore-projects, been a while 😁

Which bit is wrong sorry? I don't think a part following a colon always means it's a JSON extract path operator. I mentioned in one of my comments above:

The problem is, we can't actually know if it's a Json expression or not just by looking at the colon, because I think certain databases (e.g. informix? - the one in #1134) use colons for all delimiters - like normal column expressions. So how would we know to parse it into a JsonColumn or a normal Column?

Also, if I remember correctly, in databricks, you don't actually have to use colons at all in an expression - you can use all periods, and it'll work out which name part is the column, and then everything after that is the json extract path!

Greetings!

Which bit is wrong sorry?

a:b shall be parsed as a JSon Expression, not as a Column.
a.b is a column. And a:b.c can be a table name (on Informix).

This is how it works now (after my corrections).

Greetings!

Which bit is wrong sorry?

a:b shall be parsed as a JSon Expression, not as a Column. a.b is a column. And a:b.c can be a table name (on Informix).

This is how it works now (after my corrections).

Ah ok I see! I never actually ended up using the captured name part delimiters, but I think this still might affect our code, I'll have a look! Thanks for letting me know 😄