abundant-blush

Is there a way of formatting data at the collection level on certain queries?

I have a use-case where I have a metadata field that is json, it contains a structure that holds foreign key references to other tables/collections. This can be things like people/location/attachment references. In postgres I've created a view which enriches this data so when you load the row's metadata it would have the fresh data. Eg if a person changes their name, it would be updated automatically. Is there anything in tanstack/db & tanstack/react-db that can do something like this? I was hoping there'd be some type of onSelect? The only approach I've found so far is running the first live query, extracting the metadata from all objects returned then batching those to fetch the additional items to then rebuild it. Since this would be somewhat heavy compared to other queries where this wouldn't be needed it would be good to only trigger this if that field is requested.

4 Replies

abundant-blushOP•7d ago

Here's an example of the metadata, the top-level field keys are references to custom fields in another table which don't need to be matched, but the type/values map to other tables eg lookup_location = public.location table.

{
  "1bc25500-bf50-4128-9659-f0719be8b3df": {
    "type": "text",
    "value": "lorem ipsum dolor sit amet consectetur adipiscing elit sed do eiusmod tempor incididunt ut labore et dolore magna aliqua ut enim ad minim veniam quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat duis aute irure dolor in reprehenderit voluptate velit esse cillum dolore fugiat nulla pariatur excepteur sint occaecat cupidatat non proident sunt culpa qui officia deserunt mollit anim id est laborum"
  },
  "2681dfec-057b-41d5-89cc-0596be64b89e": {
    "type": "attachment",
    "value": [
      "c66af3e2-d1a1-4185-83ee-9b829b6a09e7",
      "7dd73381-1311-47e9-aabc-957f704c5179",
      "8a17ef84-6302-4fa4-9a6f-6920d614a3cd"
    ]
  },
  "5bf6105c-7a48-4085-810d-af15311fa603": {
    "type": "signature",
    "value": "8aeba205-54df-4297-a4cd-b965e55bf65f"
  },
  "8a8d6a66-aeff-4e52-964a-4be48650d27f": {
    "type": "lookup_person",
    "value": "4181abb5-3083-44dd-a6d2-69bccd24da92"
  },
  "8ed756d2-1f43-4950-965f-58bc3e2b4a55": {
    "type": "money",
    "value": {
      "amount": 31155,
      "currency": "AUD"
    }
  },
  "e3ecb195-97fc-4eb1-bc83-bbf3ea48f0ca": {
    "type": "lookup_location",
    "value": "868589bd-717a-4730-8f39-6a9ee7695130"
  }
}

{
  "1bc25500-bf50-4128-9659-f0719be8b3df": {
    "type": "text",
    "value": "lorem ipsum dolor sit amet consectetur adipiscing elit sed do eiusmod tempor incididunt ut labore et dolore magna aliqua ut enim ad minim veniam quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat duis aute irure dolor in reprehenderit voluptate velit esse cillum dolore fugiat nulla pariatur excepteur sint occaecat cupidatat non proident sunt culpa qui officia deserunt mollit anim id est laborum"
  },
  "2681dfec-057b-41d5-89cc-0596be64b89e": {
    "type": "attachment",
    "value": [
      "c66af3e2-d1a1-4185-83ee-9b829b6a09e7",
      "7dd73381-1311-47e9-aabc-957f704c5179",
      "8a17ef84-6302-4fa4-9a6f-6920d614a3cd"
    ]
  },
  "5bf6105c-7a48-4085-810d-af15311fa603": {
    "type": "signature",
    "value": "8aeba205-54df-4297-a4cd-b965e55bf65f"
  },
  "8a8d6a66-aeff-4e52-964a-4be48650d27f": {
    "type": "lookup_person",
    "value": "4181abb5-3083-44dd-a6d2-69bccd24da92"
  },
  "8ed756d2-1f43-4950-965f-58bc3e2b4a55": {
    "type": "money",
    "value": {
      "amount": 31155,
      "currency": "AUD"
    }
  },
  "e3ecb195-97fc-4eb1-bc83-bbf3ea48f0ca": {
    "type": "lookup_location",
    "value": "868589bd-717a-4730-8f39-6a9ee7695130"
  }
}

vicious-gold•6d ago

I'm confused what you're trying to do — format what?

abundant-blushOP•6d ago

I'm trying to convert the uuid in the value of these jsonb fields into a reference from another collection. Eg if I have a location collection I'd like to extract out that value which is just a plain string in that object to the full record details so I can display the locations name for example. But that big jsonb blob is on every row of the items table so I'd like to do it in one sweep instead of needing to do a follow up parsing.

vicious-gold•6d ago

oh so you're trying to normalize the data? so you're fetching a big blog of denormalized data and you want to split it out to different collections?

Gaming

Programming

Is there a way of formatting data at the collection level on certain queries?

Did you find this page helpful?