Access to nested JSON from SQL query

Queries and Resources
1

Hello,

I have a SQL query as data source for my table component. One of the columns is a JSON stored as varchar(max). I want to access the json data and display it in my table column instead of the whole JSON.
I've read all the topics about this problem but I can't seem to solve it. Here's an example of my JSON, but the EnvGroupList is not fixed and the STAGING part, for example, may be missing from the list.

{
  "dataIntegrationStep":"IMPORT",
  "ruleName":"REF FLIGHT SQL PROD",
  "ruleDescription":"REF FLIGHT SQL PROD DESCRIPTION",
  "enabled":true,
  "dataTypes":[
     "EVENT",
     "EFB"
  ],
  "dimensions":[
     "COMPLETENESS",
     "TIMELINESS"
  ],
  "groups":[
     "Descent",
     "Data_Source"
  ],
  "envGroupList":[
     {
        "id":"STAGING",
        "envList":[
           {
              "id":"abc-staging"
           },
           {
              "id":"def-staging"
           }
        ]
     },
     {
        "id":"INTEGRATION",
        "envList":[
           {
              "id":"ghi-integration"
           },
           {
              "id":"fyz-integration"
           }
        ]
     },
     {
        "id":"PRODUCTION",
        "envList":[
           {
              "id":"ttt"
           },
           {
              "id":"okl"
           },
           {
              "id":"mpc"
           }
        ]
     }
  ],
  "ruleDefinition":{
     "ruleType":"REF_FLIGHT_SQL",
     "context":{
        "sql":"select * from test"
     }
  }
}

here is my table :

Capture d'écran 2024-06-17 154625
Capture d'écran 2024-06-17 1546251582×385 26.2 KB

2

Hi there @AntoineOA,

You can specify the exact data point within your column's value (also depending on the type of column you use). E.g.

image
image545×792 30.2 KB

Hope that helps!

3

Hi @MiguelOrtiz, it's not working on my side. I can access to {{currentSourceRow.json}} but after it's empty. :smiling_face_with_tear:

4

Hi Antoine,

Strange...

Would you mind sharing a screenshot of your table's state > data showing a sample of how your json info is being read by Retool?

On another thought, I just checked one of my apps where I have a similar set up like yours and just realized I'm doing it differently.

  1. Create a new column, within column source select your Json value and select the desired column type (e.g. text or number)
  2. Within mapped value you can use {{ item }} to refer to the value, e.g {{ item.ruleDefinition.ruleType }}

If this doesn't work, then most likely retool is not transforming your data as an array with key values... the screenshot may help to troubleshoot.

To use my example, here is my data source, where my json column is notes:

image

And I can add a column in my table and refer to any of those key values:

image

5

Hi @AntoineOA,

If you're storing it as a string, it is likely coming out as a string instead of a JSON object. Try changing the "value" to

{{ JSON.parse(currentSourceRow.json).ruleDefinition.ruleType }}

JSON.parse convert a string into an actual object that you can reference the properties of.

2 Likes
6

Hi @MikeCB, thank you for your answer. It works!!!

1 Like
7

Hello @MiguelOrtiz , as @MikeCB said I had to add the JSON parser. That is why I was no able to make your solution work. Thank you for your time!!

1 Like
8

Ah yes, very good catch @MikeCB.

Glad it is working!

1 Like