0

I understand that there is no point in trying to engineer a relational database into a graph database. They are both inherently different and thus their data should be organized differently.

However, I'm building a database documentation app using Neo4j. In this app, a user would be able to upload tables from a SQL database. One feature is that a user would be able to preview the first 10 rows of any table they choose.

My problem is: I don't know how to structure a relational database in my Neo4j database, such that I could write Cypher query to print out a table in relational format.

So far I tried:

(Database)-[HAS_TABLE]->(Ex_Table)
                                 `–[HAS_FIELD]->(id) ––––––[HAS_CELL]->(1)
                                 `–[HAS_FIELD]->(name)–––––[HAS_CELL]->(bob)
                                 `–[HAS_FIELD]->(password)–[HAS_CELL]->(pass)

and 

(Database)-[HAS_TABLE]->(Ex_Table)
                                 `–[HAS_FIELD]->(id) –––––––\
                                 `–[HAS_FIELD]->(name)–––(HAS_ROW)->({id:1,name:bob,password: pass})
                                 `–[HAS_FIELD]->(password)––‘

Question: Are these correct? If so, how could I query Ex_Table using Cypher to print the first 10 rows? If not, how should I structure and query to get that result?

Note: I have to use a graph database.

Improve this question
2
  • Is printing out the first 10 lines your only use case? If so, then just store the first 10 lines of each table in a string property of the neo4j node for that table. Commented Feb 4, 2020 at 1:44
  • @cybersam For now, that's my only use case, but it would be nice to have a flexible solution, in case I wanted add/remove rows from the preview Commented Feb 4, 2020 at 13:55

1 Answer 1

Reset to default
1

I think you need more meta data round your graph to really make this work. I would adopt this approach to storing your data:

create pth= (d:Database {name: 'MyDB'})-[:HAS_TABLE]->(t:Table {name: 'Users'}),
(t)-[:HAS_COLUMN {order: 1}]->(c1:Column {name: 'Id'}),
(c1)-[:HAS_VALUE {row: 1}]->(v1:Value {value: '1'}),
(c1)-[:HAS_VALUE {row: 2}]->(v2:Value {value: '2'}),
(t)-[:HAS_COLUMN {order: 2}]->(c2:Column {name: 'Name'}),
(c2)-[:HAS_VALUE {row: 1}]->(v3:Value {value: 'Bob'}),
(c2)-[:HAS_VALUE {row: 2}]->(v4:Value {value: 'Jim'}),
(t)-[:HAS_COLUMN {order: 3}]->(c3:Column {name: 'Password'}),
(c3)-[:HAS_VALUE {row: 1}]->(v5:Value {value: 'password1'}),
(c3)-[:HAS_VALUE {row: 2}]->(v6:Value {value: 'SafePassword'})

This will give you a graph that looks like this: enter image description here

Now you can retrive your table structure with a query like this:

match (t:Table {name: 'Users'})-[r:HAS_COLUMN]->(c:Column)
return t.name, c.name
order by r.order

To retrieve the sample rows for a table use a query like:

// Get the column names as a list
match (t:Table {name: 'Users'})-[r1:HAS_COLUMN]->(c:Column)
with 0 as rownum, r1.order as colnum, c.name as val order by colnum
with rownum, collect(val) as vals
return rownum, vals
union // will append the results of the 2nd match to the first
// Get the sample data values
match (t:Table {name: 'Users'})-[r1:HAS_COLUMN]->(c:Column)-[r2:HAS_VALUE]->(v:Value)
with r1.order as colnum, r2.row as rownum, v.value as val order by colnum
with rownum, collect(val) as vals order by rownum
return rownum, vals

which will collect the value nodes into the correct order by row.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thank you! That was really helpful. In the table that returns from the query, would there be a way for each column to correspond to a column from the relational database? In other words, to have columns: rownum, id, name, and password? I tried indexing vals with colnums but each time I include colnums in the second WITH clause, vals splits each index into its own row. @Marj
@dannyvelasquez In short, yes. The list of column data needs to be unwound from a list into individual items. It's 23:00 here and I need my beauty sleep. I'll have a look in the morning :-)
no worries! I just realized I wouldn't even need that solution. Yours works perfectly as is :) @Marj
Phew - glad about that as there's actually now way to pivot the output in Cypher. (You could write your own stored procedure.) I've edited the query to return the sample data so that the first row contains a list of column names.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.