How to Index Storage JSON for Custom SQL Query?

Runtime Framework
,
1

I have a 2 player server authoritative game and I’m storing all the game states in a “MultiplayerGames” collection as JSON in the Storage value field. The JSON for each game state looks something like this …

{
  "completed": false,
  "gameId": "7cd0afa8-858c-4005-912f-bab76deca29b",
  "gameRules": 0,
  "players": [
    {
      "userId": "dd7ee9f2-7273-48f2-bdbd-e6ef38c1aa19",
      "name": "Pete"
    },
    {
      "userId": "4589f86b-1866-422f-b990-cccd239c9ff3",
      "name": "Spuddy",
    }
  ],
  "roundNum": 8,
  "roundStartTime": "2023-05-03T01:02:28Z"
  // the rest has been removed
}

I’ve written a custom RPC in GoLang that returns all the games that a particular player is involved in, which means either players[0].userId or players[1].userId in the above JSON matches the player’s userId who’s calling the RPC from the Unity client. Here’s the GoLang code I’m using to query the database:

	userId, _ := ctx.Value(runtime.RUNTIME_CTX_USER_ID).(string)

	query := `
	SELECT collection, key, value
	FROM storage
	WHERE collection = $1
	AND
	value->'players' @> '[{"userId":"` + userId + `"}]'
	LIMIT 100
	`
	if rows, err := db.QueryContext(ctx, query, "MultiplayerGames"); err == nil {
	// process the results of the query
	}

First of all, does this query look correct ? I’m very new to SQL and I’ve spent the last couple of hours scouring the internet for example code and I’ve used trial and error until something works and this does seem to do the job but maybe someone more experienced with SQL queries can confirm. Also, I couldn’t find any way to inject the userId into the query string using $2 so added it manually to the string with '[{"id":" + userId + "}]' Is there an easier way of doing it with the $ symbol ?

Okay, assuming my query is fine, can someone show me how to index the players array so the search is optimized for when the number of games in storage runs into the thousands ? The players array will always be 2 elements, one for each player if that helps with the indexing ? Thanks !

2

Hello @Spuddy,

Before we go into the SQL query correctness (which looks ok at a glance) I’d suggest we take a step back.

We discourage devs from writing custom SQL in Nakama (for multiple reasons, including performance concerns), but allow it as part of the built in APIs as an escape hatch in case there’s a particular need not covered by the current server functionality.

For this particular case, my suggestion would be to have an additional storage object per player that keeps track of the matches they’re involved in by referencing their corresponding match state storage object key, something like:
{"matches": [<key1>, <key2>]]}. So whenever you first create your match state object, you’d also create or update these objects for each of the involved players.

The storage engine APIs are all optimised to make use of existing indices, although you’d need some additional logic to maintain these objects, the existing APIs should allow you to read/write multiple objects with one operation, also making it atomic.

This way you can easily retrieve the game states for a given player, avoid the custom SQL query and ensure that the object retrieval will perform well, without having to create additional indices.

Hope this helps.