I have a question about the function of the agtype json operator in version 1.5.0

[shinhanbyeol]

i have a question about the phenomenon found in the version of age 1.5.0 The json operator function was fine in 1.4.0 as below query select properties->'ott' from ott_service_1.ott_company;

However, starting with 1.5.0 and later versions When you create a query, such as version 1.4.0 ERROR: invalid input syntax for type agtype error occurs, however select properties-> 'ott'::text as ott_company_name from netflix_2.ott; I did type casting like this, and the problem was solved.

Is this the phenomenon intended in 1.5.0? and Will this continue in future versions?

[jrgemignani]

@shinhanbyeol What version of PG is this on, what version of PG did it previously work on?

[shinhanbyeol]

@jrgemignani 1.5 ,1.4 Both were done in pg 14 version

[jrgemignani]

@shinhanbyeol There appears to be something weird with the definition of -> @Zainab-Saad Could you take a look?

[jrgemignani]

psql-16.1-5432-pgsql=# select agtype_object_field(properties, 'name') from xyz._ag_label_vertex;
 agtype_object_field
---------------------
 "John"
(1 row)

psql-16.1-5432-pgsql=# select agtype_object_field_agtype(properties, 'name') from xyz._ag_label_vertex;
ERROR:  invalid input syntax for type agtype
LINE 1: select agtype_object_field_agtype(properties, 'name') from x...
                                                      ^
DETAIL:  Expected agtype value, but found "name".
CONTEXT:  agtype data, line 1: name
psql-16.1-5432-pgsql=#
psql-16.1-5432-pgsql=# select properties->'name' from xyz._ag_label_vertex;
ERROR:  invalid input syntax for type agtype
LINE 1: select properties->'name' from xyz._ag_label_vertex;
                           ^
DETAIL:  Expected agtype value, but found "name".
CONTEXT:  agtype data, line 1: name
psql-16.1-5432-pgsql=#

[Zainab-Saad]

@shinhanbyeol There appears to be something weird with the definition of -> @Zainab-Saad Could you take a look?

Yes, looking into this

[jrgemignani]

@Zainab-Saad Thank you!

[Zainab-Saad]

The error you are getting is because the operator -> is overloaded (in version 1.5.0) with the following possible operands agtype -> agtype agtype -> text agtype -> int

In the query select properties->'ott' from ott_service_1.ott_company;, 'ott' is of the type unknown and when such a type is provided as second argument to the operator ->, postgres chooses the operator agtype -> agtype instead of agtype -> text. And 'ott' is not in valid agtype format (should be '"ott"'), so you get an error.

You can read why specific operator is choosen when unknown type is provided in the operator type resolution section here

[github-actions[bot]]

This issue is stale because it has been open 45 days with no activity. Remove "Abondoned" label or comment or this will be closed in 7 days.

[github-actions[bot]]

This issue was closed because it has been stalled for further 7 days with no activity.