Storing entire graph of objects and sub-objects

[tslocke]

Hi,

I was digging into EdgeDB back in one of the alphas and learned that there was no way to throw an entire object-graph (e.g. json) into the database in one go. It was possible to do that with JSON properties, but I was looking for a way to post a graph of top-level schema objects and have EdgeDB establish all the links correctly, like Datomic does.

I raised the issue on the old Spectrum chat

https://spectrum.chat/edgedb/general/how-to-store-hierarchical-document-like-data~e1b5c7b9-e0d2-48cc-9742-95aa642a8311

Has this been addressed in the beta? The announcement mentions "casting from and to JSON" but the link leads only to docs about going to json, not from.

Thanks

Tom

[elprans]

Hi Tom! Since that thread we added support for "merge" via INSERT .. UNLESS CONFLICT (docs), which greatly simplifies data deduplication necessary to transform a JSON "forest" into a proper graph. In Beta 1 you still need to decompose the incoming JSON into INSERT manually, but we are working on an extension to automate the generation of these queries (a-la datomic).

If you don't need deduplication, i.e if the entries in your JSON array don't share sub-objects, then writing data via the GrapQL mutation route might work for you.

[tslocke]

Thanks for getting back to me : )

It's more of a general question, but I think the common use-case is a strict tree, rather than a graph with shared sub-objects. e.g. I want to save an "order" with several "line-items".

I tried the graphql mutation before, but there was a restriction that I could not provide a JSON object as the value of a graphql variable, so I would end up having to serialise my JSON object to graphql syntax. Has that changed?

[clmnin]

I want to save an "order" with several "line-items"

You could use json_get and to_json in unison to do this

lines := {
  (
    FOR x in {enumerate(
      # here we have individual lines as elements in a set
      {'{"product":"Tomato","qty":2}', '{"product":"Pasta","qty":2}', '{"product":"Butter","qty":1}'}
      )}
    UNION (
      INSERT OrderLine {
        slno := x.0,
        product := (
          SELECT Product 
          FILTER .name = <str>(SELECT json_get(to_json(x.1),'product'))
        ),
        qty := <int16>(SELECT json_get(to_json(x.1),'qty')
        )
      }
    )
  )
}

Here is a complete example, implemeted in python (EdgeDB Alpha 7)

view code

import asyncio
from typing import Optional
import ujson

import edgedb
from edgedb import AsyncIOPool

EDGEDB_INSTANCE = "a7docker"
EDGEDB_DB = "bulk"

schema = """
    module default {
        # sequence to keep track of order number
        scalar type OrderNo extending sequence;
        
        type User {
            required property name -> str;
        }
        type Product {
            required property name -> str;
        }

        type OrderLine {
            property slno -> int16;
            required link product -> Product;
            required property qty -> int16;
        }
        type SaleOrder {
            required property num -> OrderNo;
            required link user -> User;
            multi link lines -> OrderLine;
            property create_date -> datetime {
                default := (SELECT datetime_current());
            }
        }
    }
    """


async def init_schema(pool: AsyncIOPool) -> None:
    async with pool.acquire() as con:
        await con.execute(f"""START MIGRATION TO {{ {schema} }}""")
        await con.execute("""POPULATE MIGRATION""")
        await con.execute("""COMMIT MIGRATION""")


async def insert_generic(
    pool: AsyncIOPool, *, dbtype: str, name: str
) -> Optional[str]:
    try:
        if dbtype not in {"Product", "User"}:
            raise Exception(f"Unsupported DB Type: {dbtype}")
        async with pool.acquire() as con:
            result = await con.query_one_json(
                f"""SELECT (
                    INSERT {dbtype} {{
                        name := <str>$name
                    }}
                ) {{
                    id,
                    name
                }}
                """,
                name=name)
        return result
    except Exception as e:
        print(e)


async def insert_sale_order(pool: AsyncIOPool, request: str):
    try:
        request_json = ujson.loads(request)
        lines = []
        for line in request_json["lines"]:
            lines.append(ujson.dumps(line))
        lines = set(lines).__repr__()
        async with pool.acquire() as con:
            result = await con.query_one_json(
                f"""SELECT (
                    INSERT SaleOrder {{
                        user := (SELECT User FILTER .name = <str>$name),
                        lines := {{
                            (
                                FOR x in {{enumerate(
                                    {lines}
                                    )}}
                                UNION (
                                    INSERT OrderLine {{
                                        slno := x.0,
                                        product := (
                                            SELECT Product 
                                            FILTER .name = <str>(
                                                SELECT json_get(
                                                    to_json(x.1),'product'
                                                )
                                            )
                                        ),
                                        qty := <int16>(
                                                SELECT json_get(
                                                    to_json(x.1),'qty'
                                                )
                                            )
                                    }}
                                )
                            )
                        }}
                    }}
                ) {{
                    id,
                    num,
                    user : {{name}},
                    lines : {{
                        slno,
                        product: {{name}},
                        qty
                    }}
                }}
                """,
                name=request_json["user"])
        return result
    except Exception as e:
        print(e)


async def main(init=True) -> None:
    async with await edgedb.create_async_pool(
        EDGEDB_INSTANCE, database=EDGEDB_DB
    ) as pool:
        if init:
            await init_schema(pool)
            print("Initilized Schema")
            await asyncio.sleep(1)
            await insert_generic(pool, dbtype="User", name="Alice")
            await insert_generic(pool, dbtype="Product", name="Butter")
            await insert_generic(pool, dbtype="Product", name="Tomato")
            await insert_generic(pool, dbtype="Product", name="Pasta Sauce")
            print("Inserted Users and Products")
        
        # assume a new order JSON was received from a client application
        new_sale_order = """{
                "user": "Alice",
                "lines": [
                    {
                        "product": "Butter",
                        "qty": 1
                    },
                    {
                        "product": "Tomato",
                        "qty": 2
                    },
                    {
                        "product": "Pasta",
                        "qty": 2
                    }
                ]
            }"""
        result = await insert_sale_order(pool, new_sale_order)
        print(f"Sale Order Create: \t{result}")


if __name__ == "__main__":
    asyncio.run(main(init=True))

@elprans are there any performance penalties in the above implementation?

[tslocke]

Thanks @clmno. My point is that if "inserting and querying hierarchical document-like data is trivial", like it says in the intro to the docs, I shouldn't have to write code like that. IIRC, other GraphQL libraries I've used let you provide a full object as value of a mutation variable. Not sure about sub-objects though.

Datomic is brilliant in this regard - you just throw a big chunk of data at it and it figures out which properties are the links, and sets them up with the correct (newly created) UIDs, and it does it to any depth of nested sub-objects. It's a full hybrid of document store and relational, both for read and write. This is what I understood/expected with EdgeDB but (in the alphas at least) it's only really true for read.

[elprans]

@clmno, you don't always have to use json_get for simple lookups, std::json supports subscript notation:

    FOR x in {enumerate(
      # here we have individual lines as elements in a set
         to_json({
             '{"product":"Tomato","qty":2}', 
             '{"product":"Pasta","qty":2}', 
             '{"product":"Butter","qty":1}'
         })
      )}
    UNION (
      INSERT OrderLine {
        slno := x.0,
        product := (
          SELECT Product 
          FILTER .name = <str>x.1['product']
        ),
        qty := <int16>x.1['qty'],
      }
    )

@tslocke

Datomic is brilliant in this regard - you just throw a big chunk of data at it and it figures out which properties are the links, and sets them up with the correct (newly created) UIDs, and it does it to any depth of nested sub-objects.

We have a plan to solve this in future releases, but the strictly-hierarchical case should be easily solvable even today with a bit of schema and query generation in Python. I'll see if I can put an example together soon.

[tslocke]

@elprans I see the beta 2 announcement today once again mentions this feature: "support for converting arbitrary strictly typed data to and from JSON via a simple cast operator"

But it is positioned more as an existing feature than a new one. Has anything has changed in regard to arbitrary JSON that spans entity boundaries?

[elprans]

There have been no big changes to JSON handling in Beta 2. I started putting together a PoC script to achieve the "throw a bunch of JSON at it" effect you're looking for.

[tslocke]

@elprans how far did you get with that PoC?

[elprans]

I've got a basic data introspection and schema generation working. I'll get back to it to clean up and post once beta 3 is out next week or the week after.