jccampagne commented on issue #1225:
URL: https://github.com/apache/age/issues/1225#issuecomment-2261489139
Hi, I am also trying to do something with list values of type agtype in SQL
(outside Cypher).
@jrgemignani in your example:
```
psql-15.4-5432-pgsql=# SELECT to_json(result) FROM cypher('graph_name', $$
WITH [1,2,3] as lst RETURN ag_catalog.agtype_out(lst)
$$) AS (result text);
to_json
-------------
"[1, 2, 3]"
(1 row)
psql-15.4-5432-pgsql=# SELECT pg_typeof(to_json(result)) FROM
cypher('graph_name', $$
WITH [1,2,3] as lst RETURN ag_catalog.agtype_out(lst)
$$) AS (result text);
pg_typeof
-----------
json
(1 row)
```
The conversion is not what we expect.
It's indeed JSON, but a **string** in JSON...
For comparison, a JSON LIST of integers (not one string) in postgres is:
```
jc=# select '[1,2,3]'::json ;
json
---------
[1,2,3]
(1 row)
jc=# select pg_typeof('[1,2,3]'::json) ;
pg_typeof
-----------
json
(1 row)
```
As per the documentation here
https://www.postgresql.org/docs/current/functions-json.html
Note the lack of quote (`"`) in the result.
It's not clear what one can do with list values. Especially with list of
edges or vertices.
Also I could not find information about this function
`ag_catalog.agtype_out()` in the documentation.
What does it do?
I managed to make the above example work as intended with `cast(... as ...)`:
```
# SELECT cast(result as json) FROM cypher('graph_name', $$
WITH [1,2,3] as lst RETURN ag_catalog.agtype_out(lst)
$$) AS (result text);
result
-----------
[1, 2, 3]
(1 row)
```
It seems to work on this simple example, but if one tries to same cast on a
list of vertices... the parsing fails because of the `::vertex` at the end...
For example, the documentation example
(https://age.apache.org/age-manual/master/functions/list_functions.html#data-setup)
fails:
```
SELECT a, cast(b as json) from cypher('graph_name', $$
MATCH (a)
WHERE a.name = 'Alice'
RETURN ag_catalog.agtype_out(a), ag_catalog.agtype_out(a)
$$) as (a varchar, b varchar);
```
will cause this error:
```
psql:sql/graph3.sql:48: ERROR: invalid input syntax for type json
DETAIL: Expected end of input, but found ":".
CONTEXT: JSON data, line 1: ...: {"age": 38, "eyes": "brown", "name":
"Alice"}}:...
```
with `to_json()` it will succeed, but as mentioned earlier, it's not what we
want:
```
SELECT a, to_json(b) from cypher('graph_name', $$
MATCH (a)
WHERE a.name = 'Alice'
RETURN ag_catalog.agtype_out(a), ag_catalog.agtype_out(a)
$$) as (a varchar, b varchar);
```
results in:
```
a
|
to_json
-----------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------
{"id": 844424930131969, "label": "Person", "properties": {"age": 38,
"eyes": "brown", "name": "Alice"}}::vertex | "{\"id\": 844424930131969,
\"label\": \"Person\", \"properties\": {\"age\": 38, \"eyes\": \"brown\",
\"name\": \"Alice\"}}::vertex"
```
Here, we clearly see the `::vertex` preventing the `cast` to work...
Note that, if the result is a list of scalars, the returned value can work
like jsonb; for example this will work:
```
SELECT a, b ->> 1 from cypher('graph_name', $$
MATCH (a)
WHERE a.name = 'Alice'
RETURN keys(a), keys(a)
$$) as (a agtype, b agtype);
a | ?column?
-------------------------+----------
["age", "eyes", "name"] | eyes
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: dev-unsubscr...@age.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
