Add agtype <-> jsonb bidirectional casts (#350) (#2361)
* Add agtype <-> jsonb bidirectional casts (issue #350)
Register explicit casts between agtype and jsonb, enabling:
SELECT properties(n)::jsonb FROM cypher(...) AS (n agtype);
SELECT '{"key": "value"}'::jsonb::agtype;
-- Use jsonb operators on graph data:
SELECT (props::jsonb)->>'name' FROM cypher(...) AS (props agtype);
Implementation uses SQL language functions that go through proven
text-intermediate paths:
agtype -> jsonb: agtype_to_json() -> json::jsonb
jsonb -> agtype: jsonb::text -> text::agtype
This approach is safe because agtype extends jsonb's binary format
with types (AGTV_INTEGER, AGTV_FLOAT, AGTV_VERTEX, AGTV_EDGE,
AGTV_PATH) that jsonb does not recognize, making direct binary
conversion unreliable. The text roundtrip handles all value types
correctly including graph types (vertex/edge properties are
extracted as JSON objects).
All 31 regression tests pass.
Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
* Address Copilot review: fix comment, add regression tests for jsonb casts
- Fix comment in agtype_coercions.sql: document json-intermediate path
(agtype_to_json -> json::jsonb) instead of incorrect "text intermediate"
- Add agtype_jsonb_cast regression test covering: string/null/array/object
agtype->jsonb, all jsonb scalar types->agtype, roundtrips, vertex/edge
->jsonb with structural key checks, NULL handling
- Register agtype_jsonb_cast in Makefile REGRESS list
Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
* Register agtype <-> jsonb casts in upgrade template
The age_upgrade regression test (added after this PR was originally
opened) verifies that every SQL object in the install SQL also appears
in the version upgrade template (age--<INIT>--<CURR>.sql). Surfaced
during rebase onto current master: agtype_to_jsonb / jsonb_to_agtype
and their casts were missing from the template, causing 4 entries in
the "missing_function" / "missing_cast" check.
Adds the two functions and two casts to the end of the template,
following the file's own "add all additions to the end of this file"
convention. Cassert installcheck now 34/34 AGE tests green (pgvector
skipped — env-only).
---------
Co-authored-by: Claude Opus 4.6 (1M context) <noreply@anthropic.com>diff --git a/Makefile b/Makefile
index 3c5a28c..7e5fb3d 100644
--- a/Makefile
+++ b/Makefile
@@ -180,7 +180,8 @@
map_projection \
direct_field_access \
security \
- reserved_keyword_alias
+ reserved_keyword_alias \
+ agtype_jsonb_cast
ifneq ($(EXTRA_TESTS),)
REGRESS += $(EXTRA_TESTS)
diff --git a/age--1.7.0--y.y.y.sql b/age--1.7.0--y.y.y.sql
index a3cdea2..5204477 100644
--- a/age--1.7.0--y.y.y.sql
+++ b/age--1.7.0--y.y.y.sql
@@ -459,3 +459,34 @@
END LOOP;
END;
$$;
+
+--
+-- agtype <-> jsonb bidirectional casts
+--
+
+-- agtype -> jsonb (explicit)
+-- Uses json intermediate (agtype_to_json -> json::jsonb) because agtype
+-- extends jsonb's binary format with types (AGTV_INTEGER, AGTV_FLOAT,
+-- AGTV_VERTEX, AGTV_EDGE, AGTV_PATH) that jsonb does not recognize.
+CREATE FUNCTION ag_catalog.agtype_to_jsonb(agtype)
+ RETURNS jsonb
+ LANGUAGE sql
+ IMMUTABLE
+RETURNS NULL ON NULL INPUT
+PARALLEL SAFE
+AS 'SELECT ag_catalog.agtype_to_json($1)::jsonb';
+
+CREATE CAST (agtype AS jsonb)
+ WITH FUNCTION ag_catalog.agtype_to_jsonb(agtype);
+
+-- jsonb -> agtype (explicit)
+CREATE FUNCTION ag_catalog.jsonb_to_agtype(jsonb)
+ RETURNS agtype
+ LANGUAGE sql
+ IMMUTABLE
+RETURNS NULL ON NULL INPUT
+PARALLEL SAFE
+AS 'SELECT $1::text::agtype';
+
+CREATE CAST (jsonb AS agtype)
+ WITH FUNCTION ag_catalog.jsonb_to_agtype(jsonb);
diff --git a/regress/expected/agtype_jsonb_cast.out b/regress/expected/agtype_jsonb_cast.out
new file mode 100644
index 0000000..49bf1f8
--- /dev/null
+++ b/regress/expected/agtype_jsonb_cast.out
@@ -0,0 +1,261 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+LOAD 'age';
+SET search_path TO ag_catalog;
+--
+-- agtype -> jsonb casts
+--
+-- String scalar
+SELECT '"hello"'::agtype::jsonb;
+ jsonb
+---------
+ "hello"
+(1 row)
+
+-- Null
+SELECT 'null'::agtype::jsonb;
+ jsonb
+-------
+ null
+(1 row)
+
+-- Array
+SELECT '[1, "two", null]'::agtype::jsonb;
+ jsonb
+------------------
+ [1, "two", null]
+(1 row)
+
+-- Nested array
+SELECT '[[1, 2], [3, 4]]'::agtype::jsonb;
+ jsonb
+------------------
+ [[1, 2], [3, 4]]
+(1 row)
+
+-- Empty array
+SELECT '[]'::agtype::jsonb;
+ jsonb
+-------
+ []
+(1 row)
+
+-- Object
+SELECT '{"name": "Alice", "age": 30}'::agtype::jsonb;
+ jsonb
+------------------------------
+ {"age": 30, "name": "Alice"}
+(1 row)
+
+-- Nested object
+SELECT '{"a": {"b": {"c": 1}}}'::agtype::jsonb;
+ jsonb
+------------------------
+ {"a": {"b": {"c": 1}}}
+(1 row)
+
+-- Object with array values
+SELECT '{"tags": ["a", "b"], "count": 2}'::agtype::jsonb;
+ jsonb
+----------------------------------
+ {"tags": ["a", "b"], "count": 2}
+(1 row)
+
+-- Empty object
+SELECT '{}'::agtype::jsonb;
+ jsonb
+-------
+ {}
+(1 row)
+
+--
+-- jsonb -> agtype casts
+--
+-- String scalar
+SELECT '"hello"'::jsonb::agtype;
+ agtype
+---------
+ "hello"
+(1 row)
+
+-- Numeric scalar
+SELECT '42'::jsonb::agtype;
+ agtype
+--------
+ 42
+(1 row)
+
+-- Float scalar
+SELECT '3.14'::jsonb::agtype;
+ agtype
+--------
+ 3.14
+(1 row)
+
+-- Boolean
+SELECT 'true'::jsonb::agtype;
+ agtype
+--------
+ true
+(1 row)
+
+-- Null
+SELECT 'null'::jsonb::agtype;
+ agtype
+--------
+ null
+(1 row)
+
+-- Array
+SELECT '[1, "two", null]'::jsonb::agtype;
+ agtype
+------------------
+ [1, "two", null]
+(1 row)
+
+-- Nested array
+SELECT '[[1, 2], [3, 4]]'::jsonb::agtype;
+ agtype
+------------------
+ [[1, 2], [3, 4]]
+(1 row)
+
+-- Empty array
+SELECT '[]'::jsonb::agtype;
+ agtype
+--------
+ []
+(1 row)
+
+-- Object
+SELECT '{"name": "Alice", "age": 30}'::jsonb::agtype;
+ agtype
+------------------------------
+ {"age": 30, "name": "Alice"}
+(1 row)
+
+-- Nested object
+SELECT '{"a": {"b": {"c": 1}}}'::jsonb::agtype;
+ agtype
+------------------------
+ {"a": {"b": {"c": 1}}}
+(1 row)
+
+-- Empty object
+SELECT '{}'::jsonb::agtype;
+ agtype
+--------
+ {}
+(1 row)
+
+--
+-- Roundtrip: jsonb -> agtype -> jsonb
+--
+SELECT ('{"key": "value"}'::jsonb::agtype)::jsonb;
+ jsonb
+------------------
+ {"key": "value"}
+(1 row)
+
+SELECT ('[1, 2, 3]'::jsonb::agtype)::jsonb;
+ jsonb
+-----------
+ [1, 2, 3]
+(1 row)
+
+SELECT ('null'::jsonb::agtype)::jsonb;
+ jsonb
+-------
+ null
+(1 row)
+
+--
+-- Graph data -> jsonb (vertex and edge)
+--
+SELECT create_graph('agtype_jsonb_test');
+NOTICE: graph "agtype_jsonb_test" has been created
+ create_graph
+--------------
+
+(1 row)
+
+SELECT * FROM cypher('agtype_jsonb_test', $$
+ CREATE (a:Person {name: 'Alice', age: 30})-[:KNOWS {since: 2020}]->(b:Person {name: 'Bob', age: 25})
+ RETURN a, b
+$$) AS (a agtype, b agtype);
+ a | b
+------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------
+ {"id": 844424930131969, "label": "Person", "properties": {"age": 30, "name": "Alice"}}::vertex | {"id": 844424930131970, "label": "Person", "properties": {"age": 25, "name": "Bob"}}::vertex
+(1 row)
+
+-- Vertex to jsonb: check structure
+SELECT v::jsonb ? 'label' AS has_label,
+ v::jsonb ? 'properties' AS has_properties,
+ (v::jsonb -> 'properties' ->> 'name') AS name
+FROM cypher('agtype_jsonb_test', $$
+ MATCH (n:Person) RETURN n ORDER BY n.name
+$$) AS (v agtype);
+ has_label | has_properties | name
+-----------+----------------+-------
+ t | t | Alice
+ t | t | Bob
+(2 rows)
+
+-- Edge to jsonb: check structure
+SELECT e::jsonb ? 'label' AS has_label,
+ (e::jsonb ->> 'label') AS label,
+ (e::jsonb -> 'properties' ->> 'since') AS since
+FROM cypher('agtype_jsonb_test', $$
+ MATCH ()-[r:KNOWS]->() RETURN r
+$$) AS (e agtype);
+ has_label | label | since
+-----------+-------+-------
+ t | KNOWS | 2020
+(1 row)
+
+--
+-- NULL handling
+--
+SELECT NULL::agtype::jsonb;
+ jsonb
+-------
+
+(1 row)
+
+SELECT NULL::jsonb::agtype;
+ agtype
+--------
+
+(1 row)
+
+--
+-- Cleanup
+--
+SELECT drop_graph('agtype_jsonb_test', true);
+NOTICE: drop cascades to 4 other objects
+DETAIL: drop cascades to table agtype_jsonb_test._ag_label_vertex
+drop cascades to table agtype_jsonb_test._ag_label_edge
+drop cascades to table agtype_jsonb_test."Person"
+drop cascades to table agtype_jsonb_test."KNOWS"
+NOTICE: graph "agtype_jsonb_test" has been dropped
+ drop_graph
+------------
+
+(1 row)
+
diff --git a/regress/sql/agtype_jsonb_cast.sql b/regress/sql/agtype_jsonb_cast.sql
new file mode 100644
index 0000000..c3a6a8e
--- /dev/null
+++ b/regress/sql/agtype_jsonb_cast.sql
@@ -0,0 +1,137 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+LOAD 'age';
+SET search_path TO ag_catalog;
+
+--
+-- agtype -> jsonb casts
+--
+
+-- String scalar
+SELECT '"hello"'::agtype::jsonb;
+
+-- Null
+SELECT 'null'::agtype::jsonb;
+
+-- Array
+SELECT '[1, "two", null]'::agtype::jsonb;
+
+-- Nested array
+SELECT '[[1, 2], [3, 4]]'::agtype::jsonb;
+
+-- Empty array
+SELECT '[]'::agtype::jsonb;
+
+-- Object
+SELECT '{"name": "Alice", "age": 30}'::agtype::jsonb;
+
+-- Nested object
+SELECT '{"a": {"b": {"c": 1}}}'::agtype::jsonb;
+
+-- Object with array values
+SELECT '{"tags": ["a", "b"], "count": 2}'::agtype::jsonb;
+
+-- Empty object
+SELECT '{}'::agtype::jsonb;
+
+--
+-- jsonb -> agtype casts
+--
+
+-- String scalar
+SELECT '"hello"'::jsonb::agtype;
+
+-- Numeric scalar
+SELECT '42'::jsonb::agtype;
+
+-- Float scalar
+SELECT '3.14'::jsonb::agtype;
+
+-- Boolean
+SELECT 'true'::jsonb::agtype;
+
+-- Null
+SELECT 'null'::jsonb::agtype;
+
+-- Array
+SELECT '[1, "two", null]'::jsonb::agtype;
+
+-- Nested array
+SELECT '[[1, 2], [3, 4]]'::jsonb::agtype;
+
+-- Empty array
+SELECT '[]'::jsonb::agtype;
+
+-- Object
+SELECT '{"name": "Alice", "age": 30}'::jsonb::agtype;
+
+-- Nested object
+SELECT '{"a": {"b": {"c": 1}}}'::jsonb::agtype;
+
+-- Empty object
+SELECT '{}'::jsonb::agtype;
+
+--
+-- Roundtrip: jsonb -> agtype -> jsonb
+--
+
+SELECT ('{"key": "value"}'::jsonb::agtype)::jsonb;
+SELECT ('[1, 2, 3]'::jsonb::agtype)::jsonb;
+SELECT ('null'::jsonb::agtype)::jsonb;
+
+--
+-- Graph data -> jsonb (vertex and edge)
+--
+
+SELECT create_graph('agtype_jsonb_test');
+
+SELECT * FROM cypher('agtype_jsonb_test', $$
+ CREATE (a:Person {name: 'Alice', age: 30})-[:KNOWS {since: 2020}]->(b:Person {name: 'Bob', age: 25})
+ RETURN a, b
+$$) AS (a agtype, b agtype);
+
+-- Vertex to jsonb: check structure
+SELECT v::jsonb ? 'label' AS has_label,
+ v::jsonb ? 'properties' AS has_properties,
+ (v::jsonb -> 'properties' ->> 'name') AS name
+FROM cypher('agtype_jsonb_test', $$
+ MATCH (n:Person) RETURN n ORDER BY n.name
+$$) AS (v agtype);
+
+-- Edge to jsonb: check structure
+SELECT e::jsonb ? 'label' AS has_label,
+ (e::jsonb ->> 'label') AS label,
+ (e::jsonb -> 'properties' ->> 'since') AS since
+FROM cypher('agtype_jsonb_test', $$
+ MATCH ()-[r:KNOWS]->() RETURN r
+$$) AS (e agtype);
+
+--
+-- NULL handling
+--
+
+SELECT NULL::agtype::jsonb;
+SELECT NULL::jsonb::agtype;
+
+--
+-- Cleanup
+--
+
+SELECT drop_graph('agtype_jsonb_test', true);
diff --git a/sql/agtype_coercions.sql b/sql/agtype_coercions.sql
index 933375f..7763360 100644
--- a/sql/agtype_coercions.sql
+++ b/sql/agtype_coercions.sql
@@ -174,6 +174,33 @@
CREATE CAST (agtype AS json)
WITH FUNCTION ag_catalog.agtype_to_json(agtype);
+-- agtype -> jsonb (explicit)
+-- Uses json intermediate (agtype_to_json -> json::jsonb) because agtype
+-- extends jsonb's binary format with types (AGTV_INTEGER, AGTV_FLOAT,
+-- AGTV_VERTEX, AGTV_EDGE, AGTV_PATH) that jsonb does not recognize.
+CREATE FUNCTION ag_catalog.agtype_to_jsonb(agtype)
+ RETURNS jsonb
+ LANGUAGE sql
+ IMMUTABLE
+RETURNS NULL ON NULL INPUT
+PARALLEL SAFE
+AS 'SELECT ag_catalog.agtype_to_json($1)::jsonb';
+
+CREATE CAST (agtype AS jsonb)
+ WITH FUNCTION ag_catalog.agtype_to_jsonb(agtype);
+
+-- jsonb -> agtype (explicit)
+CREATE FUNCTION ag_catalog.jsonb_to_agtype(jsonb)
+ RETURNS agtype
+ LANGUAGE sql
+ IMMUTABLE
+RETURNS NULL ON NULL INPUT
+PARALLEL SAFE
+AS 'SELECT $1::text::agtype';
+
+CREATE CAST (jsonb AS agtype)
+ WITH FUNCTION ag_catalog.jsonb_to_agtype(jsonb);
+
CREATE FUNCTION ag_catalog.agtype_array_to_agtype(agtype[])
RETURNS agtype
LANGUAGE c
