JSON object with id as primary key schema design

Question

I want to use an ID as the primary key in a JSON object. This way all users in the list are unique.

Like so:

{
    "user": [{
        "id": 1,
        "name": "bob"
    }]

}

In an application, I have to search for the id in all elements of the list 'user'.

But I can also use the ID as an index to get easier access to a specific user. Like so:

{
    "user": {
        "1": {
            "name": "bob"
        }
    }
}

In an application, I can now simply write user["3"] to get the correct user.

What should I use? Are there any disadvantages to the second option? I'm sure there is a best practice.

mike · Accepted Answer · 2022-04-18T14:08:13.347

It depends on what format you want objects to look like, how much processing you want to do on your objects and how much data you have.

When dealing with web data you will often see the first format. If there is a lot of data then you will need to iterate through all records to find your matching id because your data is an array. Often that query would be enforced on your lower level data set though so it might already be indexed (eg. if it is a database) so this may not be an issue. This format is clean and binds easily.

Your second option works best when you need efficiency in your lookups since you have a dictionary with key value pairs allowing for significantly faster lookups in large datasets. Putting a numeric key (even though you are forcing it to be a string) is not supported by all libraries. You can prefix your Id with an alpha value though, then you can just add the prefix when doing a lookup. I have used k in this example but you can choose a prefix that makes sense for your data. I use this format when storing objects as the json binary data type in databases.

{
    "user": {
        "k1": {
            "name": "bob"
        }
    }
}

JSON object with id as primary key schema design

1 Answers1