I need to store nodes with array of ip-addresses, ip-prefixes (::inet or ::cidr). After that I want to retrieve some nodes that have properties-prefixes from some other net-prefix.

Tried to create node\vertex

SELECT * 
FROM cypher('graph_name', $$
    CREATE (v:Vertex {
        ips: ['192.168.10.1/24', '192.168.20.2/25']
    })
$$) as (v agtype);

I had to store prefixes as strings, because AGE doesn't support ::inet or ::cidr primitives.

Tried to fetch all nodes where any of of the elements of the property array are contained in '192.168.0.0/16'::inet

SELECT * 
FROM cypher('graph_name', $$
    MATCH (v:Vertex)
    WHERE ANY(ip IN v.ips WHERE ip << '192.168.0.0/16'::inet)
    RETURN v;
$$) as (v agtype);

In this case I got an error and I tried to simplify query (just compare two strings), but got the same error at the same position (I couldn't find documentation about ANY() construction):

postgresDB=# SELECT *
FROM cypher('graph_name', $$
    MATCH (v:Vertex)
    WHERE ANY(ip IN v.ips WHERE ip == '192.168.10.1/24')
    RETURN v;
$$) as (v agtype);
ERROR:  syntax error at or near "WHERE"
LINE 4:     WHERE ANY(ip IN v.ips WHERE ip == '192.168.10.1/24')

How should I store an array of network prefixes as a property of a Node to have a way to query them with all all the ::inet type capabilities?

I need to store nodes with array of ip-addresses, ip-prefixes (::inet or ::cidr). After that I want to retrieve some nodes that have properties-prefixes from some other net-prefix.

Tried to create node\vertex

SELECT * 
FROM cypher('graph_name', $$
    CREATE (v:Vertex {
        ips: ['192.168.10.1/24', '192.168.20.2/25']
    })
$$) as (v agtype);

I had to store prefixes as strings, because AGE doesn't support ::inet or ::cidr primitives.

Tried to fetch all nodes where any of of the elements of the property array are contained in '192.168.0.0/16'::inet

SELECT * 
FROM cypher('graph_name', $$
    MATCH (v:Vertex)
    WHERE ANY(ip IN v.ips WHERE ip << '192.168.0.0/16'::inet)
    RETURN v;
$$) as (v agtype);

In this case I got an error and I tried to simplify query (just compare two strings), but got the same error at the same position (I couldn't find documentation about ANY() construction):

postgresDB=# SELECT *
FROM cypher('graph_name', $$
    MATCH (v:Vertex)
    WHERE ANY(ip IN v.ips WHERE ip == '192.168.10.1/24')
    RETURN v;
$$) as (v agtype);
ERROR:  syntax error at or near "WHERE"
LINE 4:     WHERE ANY(ip IN v.ips WHERE ip == '192.168.10.1/24')

How should I store an array of network prefixes as a property of a Node to have a way to query them with all all the ::inet type capabilities?

• postgresql
• apache-age

1 Answer 1

The best solution for me is:

create a separate table with "metadata". We can create it in a different schema - public

CREATE TABLE vertex_meta (
    id uuid PRIMARY KEY,
    vertex_id uuid,
    prefix inet
);

Create regular vertex with additional id inside properties - connection link for foreign table

LOAD 'age';
SET search_path = ag_catalog, "$user", public;


SELECT * 
FROM cypher('graph_name', $$
    CREATE (v:Vertex {
        id: '0745e0ca-97e7-4da9-98c5-87c9a283c707',
        name: 'prefix-07'
    })
$$) as (v agtype);


SELECT * 
FROM cypher('graph_name', $$
    CREATE (v:Vertex {
        id: '0845e0ca-97e7-4da9-98c5-87c9a283c708',
        name: 'prefix-08'
    })
$$) as (v agtype);

Fill the metadata-table with some data

INSERT INTO vertex_meta (id, vertex_id, prefix)
VALUES (
    uuid_generate_v4(),
    '0845e0ca-97e7-4da9-98c5-87c9a283c708',
    '192.168.10.0/24'
); 

INSERT INTO vertex_meta (id, vertex_id, prefix)
VALUES (
    uuid_generate_v4(),
    '0745e0ca-97e7-4da9-98c5-87c9a283c707',
    '192.168.20.0/24'
); 

Now we can join the graph with our meta-table

SELECT * 
FROM cypher('graph_name', $$
    MATCH (v:Vertex)
    RETURN v, v.id
$$) as graph_query(v agtype, vertex_id agtype)
JOIN public.vertex_meta
ON graph_query.vertex_id::text = vertex_meta.vertex_id::text
WHERE vertex_meta.prefix <<= '192.168.0.0/16'::inet

We have to use ::text because ag_type can't be converted into uuid out of the box

We can extend our meta-table with any type of data