How to insert/update a sub-graph in the form of complex JSON, from an GraphQL API?

[d4h0]

Hi,

I'm wondering what the best way is, to insert/update a sub-graph, if I have a complex, nested JSON object from a GraphQL API.

The EdgeDB schema is generated from the GraphQL schema, and I can generate the code that inserts/updates the data in EdgeDB.

For simple sub-graphs, this doesn't seem too difficult.

However, my sub-graph is quite complex, with many optional fields, so I'm not 100% sure what the best way is, to implement such an insert/update in a single EdgeDB query.

There was a discussion about this in the past: Storing entire graph of objects and sub-objects.

I'm wondering if anything changed since.

Right now, I see the following options:

1. Generating a single EdgeDB insert/update query. However, I'm not sure how to handle deep, optional data. Would I have to generate the query at runtime (when I know what data exists), or is there an easy way to represent this logic in a single query?
2. Using the EdgeDB's GraphQL mutation endpoints (I'd prefer, not to activate the GraphQL plugin). Inserting seems easy, however, updating potentially existing data (that is somewhere nested in the input data), seems more difficult
3. Writing many small queries, that insert/update the sub-graph (wouldn't be the end of the world, but I'd like to avoid this)

[vpetrovykh]

The overall number of possible options for inserting/updating a deep graph (forest) with nested optional branches is rather large and I'm not certain I can just give a one-size-fits-all solution. Could you provide a more concrete task you're trying to do here? Ideally with some sample data to use in examples.

I'll outline some basic thoughts on how I would approach something like this here:

1. You must know in some fashion the overall (maximal) shape you want to affect. This information affects the query that you need to build. This puts a limit on just how arbitrary the input JSON blob of data can be. If you want truly arbitrary (user-controlled) inputs, I'm afraid you'd need a layer that manages to map the input shape on EdgeDB types and basically replicates some of the GraphQL machinery.
2. Given the broad outline of the overall shape you want to be affected, do you a priori know which bits are going to need insert and update? Basically, do you actually want an upsert or do you know even before looking at the data which operation you will need for a particular branch? Things are a little simpler without "upserts", so you should minimize them, but either way, it's doable. Pay extra attention to which field turns an insert into update if an object like that exists.
   If your data is structured such that a presence of "id" field implies an update and its absence implies an insert, we don't have a simple way of doing this. I'd use a for loop to iterate over specifically the inputs that result in inserts (filter out all stuff with "id") and then union the results with the update for the inputs with "id".
3. Handling optional branches in your inputs is a little messy.
   Optional updates are simple, because if the update filter doesn't select anything, no update happens, so you just need to refer to the branch being updated as usual in the filter and if there's no JSON data for it, using json_get to access it should do the right thing.
   Optional inserts have to use a for statement iterating over the potentially empty input set and creating only the objects if necessary. This insert can also be made into an "upsert", if needed.

Since you're mixing inserts and updates you might need to make use of the top-level with block to factor out these modification queries and then build up your final results from these. Generally, for complex queries factoring things out and putting them into a with block is often helpful.

I'm sure I could provide more specific answers for a more specific task. "Optional values" can mean so many things, that it's hard to cover them all in one generic overview. Optional properties may have defaults and if you simply assign {} to them, they don't get the default value, so that's a thing to keep in mind. This kind of consideration potentially creates a whole new layer of complexity, if this is the kind of thing you need to handle.

[d4h0]

Thank you for your response, @vpetrovykh!

I think, I have to wait a week or so until I can give your suggestions a try (I'm three days into a water fast – and it turned out, programming is pretty difficult after not eating for three days...).

Just in case, it would change anything, I'll try to supply the information that was missing for my previous message:

Could you provide a more concrete task you're trying to do here? Ideally with some sample data to use in examples.

I don't own the API, so unfortunately, I can't provide sample data. But I can provide the EdgeDB schema that I've generated from the GraphQL schema.

Here is the schema for one of the GraphQL API requests:

https://gist.github.com/d4h0/525791b8be1174e0aef9db1e40569fe2

As you can see, there is quite a bit of data involved (and I wouldn't expect, that anybody spends a lot of time trying to understand what is going on). Please note, that this EdgeDB schema is generated, and that I have not imported it into EdgeDB, yet (so there might be errors).

You must know in some fashion the overall (maximal) shape you want to affect.

Fortunately, the GraphQL API follows a strict schema, so I believe there shouldn't be a problem in this regard.

[...] do you a priori know which bits are going to need insert and update? Basically, do you actually want an upsert or do you know even before looking at the data which operation you will need for a particular branch?

Basically, I'd like to synchronize the data of the GraphQL API and my EdgeDB instance. So it would always be an upsert, and I never have any idea what data changed.

Pay extra attention to which field turns an insert into update if an object like that exists.

Most generated EdgeDB objects have an api_id field (which represents an unique ID from the API), or are separate objects but really are part of an object that has an api_id (within the GraphQL API, they are part of an object with an api_id, but I moved the data to their own EdgeDB object). So that part shouldn't be difficult.

If your data is structured such that a presence of "id" field implies an update and its absence implies an insert, we don't have a simple way of doing this.

Did you mean to write "If your data isn't structured [...]"? If not, I do not really understand why api_id would make things more difficult, to be honest.

---

Right now, I think I'd have to generate a pretty complex query with many levels of nested for loops (and/or parts that are moved to a 'with' block). Somehow I hoped there would be a "magical" trick or feature that would solve this problem, but that is probably not really realistic... :)

At least I can generate the query via code, and don't have to write (and maintain) it by hand.

Thanks for your help, @vpetrovykh!