0

I'm having quite an unusual task and I'm not even sure if it's solvable.

I have a table with JSONB column which can contain arbitrary nested JSON objects. Let's call it objects table.

And I have a second table references that contains a string paths (e.g. attribute1->attribute2->attribute3, attribute4->attribute5) by which one object from objects may "reference" another object from object.

Here comes an example.

objects:

{
  "name" : "John",
  "age" : 30,
  "car" : {
    "name" : "Ford",
    "active" : true
  }
}

objects:

{
  "type" : "Ford",
  "color" : "red"
}

references:

|attribute_from|attribute_to|
|"car->name"   |"type"      |

Note that it's a very much simplified example: real objects are much more "deep" in terms of levels of nesting.

Now comes the question: is it even possible to create a self-join of objects on objects by the join-condition, defined in the references table?

I've checked this question: PostgreSQL joining using JSONB But there the join condition is "static", i.e. author always knows exactly how one objects are referenced by others. In my case, this joining condition is arbitrary and defined in a separate table...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.