How to resolve a type from an id within a JSON field in a table

[jasonwoodland]

Hey, I am trying to make the following query possible:

order(id: "T3JkZXI6MTk5OQ==") {
  items {
    quantity
    product {
      name
    }
  }
}

which would return something like:

"order": {
  "items": [
    {
      "quantity": 5,
      "product": {
        "name": "some product"
      }
    },
    {
      "quantity": 10,
      "product": {
        "name": "another product"
      }
    }
  ]
}

Seems rather simple, however, in my schema, my order table has a column named items of type json. Here's an example of what the items column on the order table would contain:

[
  {
    "quantity": 5,
    "productId": "UHJvZHVjdDox",
  },
  {
    "quantity": 10,
    "productId": "UHJvZHVjdDoyMDA="
  }
]

The items field on my OrderType is a GraphQLList(OrderItemType). Thus, my OrderItemType is a basic GraphQLObjectType with some scalar type fields which resolve as expected. However, one of the product field in my OrderItemType is a string containing a global ID of a ProductType. Currently I am only able to resolve the ID as a string, but am unsure of how to resolve it into a ProductType with join-monster? Is this even possible or should I ditch the JSON column in turn for an additional order_item table?

[nicoabie]

New maintainer here, were you able to find a solution?

[nicoabie]

Here I demonstrate this could be done using SQLITE json_each function
PG also has this function

join-monster/join-monster-sscce#3

[jasonwoodland]

Nice work! Thanks for making this example