Index the keys of an object

[nichoth]

Hello. This is probably simple, but I’ve been searching for a while and haven’t managed to figure this out. Say I have a collection that contains objects with a nested object with unknown keys, like this:

{
    users: {
        "abc123": "...",
        "def456": "..."
    }
}

I want to query in such a way that I can take a string and return any documents that contain that string as a key of the users object in the example.

For example, I want to search for user "123abc"

Thanks

[Bryan_Fauna]

Hi @nichoth ,

The best way to handle your case (i.e. you have an unbound and unknown set of key names) is to use the following schema pattern, that I am going to illustrate with an IoT use case, to show how this works.

Imagine that you have an IoT base application that deals with data logs coming from thousands of remote devices deployed in the field. There are several different types of devices deployed, and each type logs its own set of data points. Some record temperature measurements, some record humidity measurements. You want to query for all device logs by the the type of measurement they contain e.g. “get all logs that have a temperature measurement”. For example, here’s a record from a device that records temperature and humidity

{
  deviceId: 123,
  measurement: [
    { 
      key: "temp", 
      value: 60
    },
    {
      key: "humidity",
      value: 0.5
    }
  ]
}

Another device that records temperature and pressure would send in documents like

{
  deviceId: 456,
  measurement: [
    { 
      key: "temp", 
      value: 33
    },
    {
      key: "pressure",
      value: 101325
    }
  ]
}

The advantage of this pattern is that it enables you to declare an index on key names, even when you don’t know what the set of key names are going to be, which is your case exactly. As you may know, using indexes in support of your query is very important to achieving high performance and low operational costs. We are just on the verge of releasing a feature called Computed Fields, that will make it very easy for you to use this pattern. Consider this FSL snippet that defines the DeviceLog collection and the index named measuremnentsByType

collection DeviceLog { 
  computed readingKeys = doc => doc.reading.map(.key)

  index measuremnentsByType {
    terms [mva(.readingKeys)]
  }
}

The combination of the computed field and the index will allow you use the following queries

DeviceLog.measuremnentsByType( "temp" )

{
  data: [
    [
      {
        id: "387392393155969092",
        coll: DeviceLog,
        ts: Time("2024-01-19T22:57:05.750Z"),
        deviceId: 123,
        reading: [
          {
            key: "temp",
            value: 60
          },
          {
            key: "humidity",
            value: 0.5
          }
        ]
      },
      null
    ],
    [
      {
        id: "387392393155970116",
        coll: DeviceLog,
        ts: Time("2024-01-19T22:57:05.750Z"),
        deviceId: 456,
        reading: [
          {
            key: "temp",
            value: 33
          },
          {
            key: "pressure",
            value: 101325
          }
        ]
      },
      null
    ]
  ]
}

As I say, Computed Fields will be released very soon. If you hang on a bit you can use this pattern within a week or two. If you need to make immediate progress we can show you some workarounds.

[nichoth]

Thanks @Bryan_Fauna. I had landed on a similar solution, changing the shape of the data a little bit to facilitate queries.