Cast agtype to array

[rafsun42]

Is your feature request related to a problem? Please describe. I am trying to cast some properties of a vertex to Postgres' type. That property can be an array of any type. Currently, I only see casting is available for integer array and scalar type only.

Describe the solution you'd like I would like to cast properties to native Postgres array.

Describe alternatives you've considered If I can get the entire property as a JSONB object, I can still make my query work.

Additional context N/A

[jrgemignani]

@rafsun42 You mean casting some property in the properties object, that is an array, as an array?

[rafsun42]

@jrgemignani Yes. I have a property- {array: ['a', 'b', 'c']} I would like to return ['a', 'b', 'c'] as text[] array.

[jrgemignani]

@jrgemignani Yes. I have a property- {array: ['a', 'b', 'c']} I would like to return ['a', 'b', 'c'] as text[] array.

Should we generalize it so that it can be used for other types of arrays? Or, will it always be text?

[rafsun42]

We should generalize. Any agtype (scalar, object, array) should be mapped to native Postgres type. It will be useful when user wants to pass agtype as an argument to a non-age function.

I will edit this issue to make it generalized.

[rafsun42]

@jrgemignani I am trying to return a property as json in the following example. But it throws an 'unable to cast' error.

I have this vertex:

CREATE (m:Person
        {
                name: 'Matt',
                edu: [
                        {school:'NSU', cgpa: 4.4},
                        {school:'York', cgpa: 4.3}
                ]
        }
) RETURN (m.edu)

The following queries work (returning 'edu' as 'agtype'):

test=# SELECT * FROM cypher('my_graph', $$ MATCH (m: Person) RETURN m.edu $$) as (a agtype);
                                 a                                 
-------------------------------------------------------------------
 [{"cgpa": 4.4, "school": "NSU"}, {"cgpa": 4.3, "school": "York"}]
(1 row)

But, the the following does not work (returning 'edu' as 'json'):

test=# SELECT * FROM cypher('my_graph', $$ MATCH (m: Person) RETURN m.edu $$) as (a json);
ERROR:  cannot cast type agtype to json for column "a"
LINE 1: SELECT * FROM cypher('my_graph', $$ MATCH (m: Person) RETURN m.e...

I found an workaround. The following querying wraps the property into agtype_out to make it return as a json string. It can then be parsed into json using Posgres' json functions.

SELECT * FROM cypher('my_g', $$ MATCH (m: Person) RETURN ag_catalog.agtype_out(m.edu) $$) as (a text);

But, I think the failed query should work without the workaround. You mentioned that the functionality is already there, but sadly I cannot find it. I found datum_to_agtype but not vice versa. Is there a function 'agtype_to_datum', perhaps named differently?

[jrgemignani]

@rafsun42 Sorry for the delay.

There is a function agtype_value_to_agtype is that what you need?

I'm not sure about the JSON issue above. However, I can add that AGTYPE is based off of JSONB, not JSON. JSONB is the binary form of JSON. So, AGTYPE is the serialized AGTYPE_VALUE structure. AGTYPE is what will be stored, AGTYPE_VALUE is the in memory structure, neither are text.

Hope that is helpful.

[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 is stale because it has been open 60 days with no activity. Remove "Abondoned" label or comment or this will be closed in 14 days.

[github-actions[bot]]

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