PostgreSQL JSON Graph | D E W = 露の世

转载: dongxuyang1985/sql-graph-database: A Simple Graph Database implemented with SQL

# Create Schema

CREATE TABLE IF NOT EXISTS node (
    node_id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    properties JSONB
);

CREATE TABLE IF NOT EXISTS edge (
    edge_id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    source_id BIGINT NOT NULL,
    target_id BIGINT NOT NULL,
    properties JSONB,
    FOREIGN KEY(source_id) REFERENCES node(node_id),
    FOREIGN KEY(target_id) REFERENCES node(node_id)
);

CREATE INDEX IF NOT EXISTS idx_edge_source_id ON edge(source_id);
CREATE INDEX IF NOT EXISTS idx_edge_target_id ON edge(target_id);

# Insert Node and Edge

INSERT INTO node(properties) VALUES('{"Label":"Person", "Name":"张三", "Age": 22}');
INSERT INTO node(properties) VALUES('{"Label":"Person", "Name":"李四", "Age": 30}');
INSERT INTO node(properties) VALUES('{"Label":"Person", "Name":"王五", "Age": 28}');



INSERT INTO edge(source_id, target_id, properties)
VALUES((SELECT node_id FROM node WHERE properties ->> 'Name'='张三'),
       (SELECT node_id FROM node WHERE properties ->> 'Name'='李四'), '{"Label":"关注", "Degree": 80}');
INSERT INTO edge(source_id, target_id, properties)
VALUES((SELECT node_id FROM node WHERE properties ->> 'Name'='李四'),
       (SELECT node_id FROM node WHERE properties ->> 'Name'='王五'), '{"Label":"关注", "Degree": 90}');
INSERT INTO edge(source_id, target_id, properties)
VALUES((SELECT node_id FROM node WHERE properties ->> 'Name'='张三'),
       (SELECT node_id FROM node WHERE properties ->> 'Name'='王五'), '{"Label":"关注", "Degree": 60}');

# Search Node and Edge

SELECT properties FROM node WHERE node_id = 1;
SELECT properties FROM node WHERE properties ->> 'Name'='张三';
SELECT properties FROM node WHERE properties ->> 'Label'='Person';


SELECT * FROM edge WHERE source_id = 1 AND target_id = 2;

# Update Node and Edge

UPDATE node
SET properties = jsonb_set(properties, '{Age}', '25')
WHERE node_id = 1;

UPDATE node
SET properties = jsonb_set(properties, '{Sex}', '"男"')
WHERE properties ->> 'Name'='张三';


UPDATE edge
SET properties = jsonb_set(properties, '{Degree}', '77')
WHERE source_id = 1 AND target_id = 2;

# Traverse

WITH RECURSIVE traverse(id, relation, hops) AS (
  SELECT node_id, CAST(node_id AS TEXT), 0 FROM node WHERE properties ->> 'Name'='张三'
  UNION ALL
  SELECT target_id, relation||'->'||target_id, hops + 1 FROM edge JOIN traverse ON source_id = id
)
SELECT id, relation, hops
FROM traverse
WHERE id = (SELECT node_id FROM node WHERE properties ->> 'Name'='王五');

# Delete Node and Edge

DELETE FROM node WHERE node_id = 1;

DELETE FROM node WHERE properties ->> 'Name' = '张三';

DELETE FROM node
WHERE properties ->> 'Name' = '张三'
AND properties ->> 'Label' = 'Person';


DELETE FROM edge WHERE source_id = 1 AND target_id = 2;

db