SuperNimbusKnowledge Base

Handling JSON

Querying JSON

Our app currently is only handling columnar data from the database. We have not dealt with any custom JSON fields. In a real-world scenario, this data type would store the player’s game data.

It is not needed when listing players in the main table, so is not specified in the ‘get_players’ query to reduce the payload size as it could be quite large in a real-world case for a full list.

Instead, we will have a separate ‘get_player_data’ query that will only fetch data for a single player based on their playerId when we open a modal for the player. Create a new query and paste in the following SQL. Set the query to only run when manually triggered.

SELECT data FROM players where player_id={{playerTable.selectedRow.player_id}} LIMIT 1

Our player data consists of a JSON object that tracks in-game achievements and the alignment of their game character.

Displaying JSON

We will add a new modal for player details that is opened with a table action called ‘Details’, like we did with update and delete. As covered in the previous section, this involves:

Add a container to the modal and add-on a header, changing the title text to ‘Player Data’.

Search for JSON in components and add a JSON editor to the container. Paste the following code snippet into the ‘Value’ field of the JSON editor.

{{JSON.parse(get_player_data.data.data[0])}}

Breaking down this snippet, ‘get_player_data’ is a reference to the query. The first ‘.data’ is the result of the query request, which is how Retool handles query data. The second ‘.data’ refers to the column in our database that was fetched in the request. The final ‘[0]’ is required as Retool will always return results in a list format.

As our query has a limit of one, it will only have one item in the list so we reference the first item.

This JSON component is useful for displaying a data structure during the early stages of development when it has not been finalized. However it may not be practical to use as a UI for updating values. We will now delete the JSON component and replace it with new UI elements.

The design of the UI panel should be determined by the JSON structure. Our player data JSON consists of:

  1. An object of booleans/binary values.
  2. numeric value.

Retool has components that cover a massive range of use cases, so we will demonstrate a few here to so what is possible with this data structure.

The set of booleans for in-game achievements can be displayed with ‘select’ inputs such as the check box and switch.

{
	"ACHIEVEMENTS": {
		"FINISHED_TUTORIAL" : 0, 
		"ADDED_FRIEND": 0, 
		"PERFECT_GAME" : 1
	}
}

The ‘Alignment’ field is a numerical value that represents whether the player’s character is good, neutral or evil.

For this example we will represent this field with a dropdown list or radio options, but we will use a segmented control component.

{
            "ALIGNMENT": 2
}

After breaking down our data structure, our UI panel view needs two containers, one for each of the above sections.

Add two containers, giving them appropriate titles.

Note
A container header needs to be added under the components ‘Add-ons’.

To adjust the size of the modal to fit the containers, set its height value to 500px for now and adjust later as needed.

For the Achievements, we will look at three different components to handle toggling values.

On the components panel, scroll down to the ‘Select inputs’. Drag a checkbox and switch components into the achievements container. Click on the labels of each component and position them to the left.

Change the label text to ‘Finished Tutorial’ and ‘Added Friend’ on each component.

For the third boolean, we will use the ‘Toggle Button’ component. Select it from the components panel and drag it under the previous two.

To customize the button for our use, copy the following into the ‘Text’ attribute.

{{ self.value ? 'Perfect Game' : 'Not a Perfect Game' }}

For the add-ons, we will change them from the default arrows to thumbs up/down. Click on ‘False icon’, search for ‘thumb’ and select a thumb down icon. Do similarly for the ‘True icon’.

To link these components to the values in our JSON, paste the following code snippets into their value attributes.

{{JSON.parse(get_player_data.data.data[0]).ACHIEVEMENTS.FINISHED_TUTORIAL}}
{{JSON.parse(get_player_data.data.data[0]).ACHIEVEMENTS.ADDED_FRIEND}}
{{JSON.parse(get_player_data.data.data[0]).ACHIEVEMENTS.PERFECT_GAME}}

For the Alignment, the fact that it can have three values suggests that we can use the ‘segmented control’ component. Drag this component into the alignment container and add a third option. The label add-on can be removed as it is present in the container title.

For each of the three options, change their values and labels to the following:

0 ‘Good’

1 ‘Neutral’

2 ‘Evil’

Add the following code snippet to the default value of the component.

{{JSON.parse(get_player_data.data.data[0]).ALIGNMENT}}

Now that the panel is built and is referencing the get_player_data query, we need to have the new modal open when we click the table action and trigger the ‘get_player_data’ query. Add these two click action event handlers to tables ‘Details’ action.

Updating JSON

Next we need to be able to update the values. We will create two queries, one to update the values and another to reset them to default values, resetting the players account.

In the components panel, add two buttons to the bottom of the UI panel and change their text to ‘Update’ and ‘Reset’.

In the code panel, add two new queries ‘update_player_data’ and ‘reset_player_data’. Copy the following SQL statement into the new queries

UPDATE players SET data = 
  jsonb_build_object(
    'ACHIEVEMENTS', jsonb_build_object(
      'FINISHED_TUTORIAL', CAST({{ checkbox1.value ? 1 : 0 }} AS INTEGER),
      'ADDED_FRIEND', CAST({{switch1.value  ? 1 : 0 }} AS INTEGER),
      'PERFECT_GAME', CAST({{toggleButton1.value  ? 1 : 0 }} AS INTEGER)
    ),
    'ALIGNMENT', CAST({{segmentedControl1.value}} AS INTEGER)
  )
WHERE player_id = {{playerTable.selectedRow.player_id}};

UPDATE players SET data =  
  jsonb_build_object(
    'ACHIEVEMENTS', jsonb_build_object(
      'FINISHED_TUTORIAL', false,
      'ADDED_FRIEND', false,
      'PERFECT_GAME', false
    ),
    'ALIGNMENT', 1
  )
WHERE player_id = {{playerTable.selectedRow.player_id}};

Examining the two queries, we see the first one updates the data field to values referenced in the Retool components. In the second query, most of the references have been replaced with default values.

We need to add click event handlers to the buttons to trigger the queries then success event handlers to the queries to trigger ‘get_player_data’. The latter is to ensure that the changes are correctly displayed after the update.

To test, display the details of a player and reset them to default. The update button can be used to set the values to what is required.

As a final addition, update the modal text to the following, so it is obvious what player is being updated.

### Player Data : {{ playerTable.selectedRow.player_name }}

Summary

Now we can display and update JSON data with our application.

From here, we group some of our existing components into a Retool Module.

Jump to section