Query JSON with SQL doesn't parse json values correctly

App Building
1

I have a GraphQL (I'm a bit of noob when it comes to GraphQL) query set to retrieve data from a resource, and then a Query JSON with SQL to extract relevant data out of the GraphQL data. It seems the data contained in the GraphQL data is stored as a string, but is formatted as a json, so if I convert and try to extract a value which has the same name as a retool function, it doesn't get parsed properly.

  select
  data :: json->value->label->text as new_val,
  data :: json->previous_value->label->text as prev_val

If I comment out the 2nd line, the query runs properly, but I need to extract that "value" from the data.

Am I doing something incorrect in my query or is it just an issue with the Query JSON with SQL functionality?

2

@will_root

Hey there :wave: Just to clarify -- is the code block you shared your Query JSON with SQL query? I am also curious, are you seeing any errors when you run the query as is?

3

Hey @lauren.gus

Yea, it's part of the query. I get the following error if I include data :: json->value->label->text as new_val:

message:"Parse error on line 5:
...ta,  data :: json->value->label->text a
----------------------^
Expecting 'LITERAL', 'BRALITERAL', 'LPAR', 'NUMBER', 'IF', 'REPLACE', 'DATEADD', 'DATEDIFF', 'INTERVAL', got 'VALUE'"

The query runs correctly if I exclude data :: json->value->label->text as new_val,, so to me it seems that it's some kind of parsing problem because value is also a Retool function (but a column I need from my data source).

4

@will_root

Ah, got it! Thank you for breaking that down further for me. Confirming that value is a reserved word in Retool. Sorry for the confusion! I would recommend running the first select and then running a transformer or JS query to finish formatting the returned data.