Query JSON with SQL returns nothing

App Building
1

Can anyone suggest a reason why my query does not work? I have attempted with .data and .dataArray. I have experimented with bizarre punctuation. I have experimented with differing formats of data. There is only an empty array produced.

image
image526×537 30.7 KB

Much gratitude as always...

2

When you hover over the contact_query.dataArray what does it show? Have you tried contact_query.data instead?

3

Both permutations show a nice full data array when hovered over...

4

Not to be Captain Obvious here, but does the array in fact contain a record where firstname is tom and not TOM or Tom (or Thomas)...you get my point, but can you share the data in the array? Or if not, what happens if you remove the where clause?

1 Like
5

Input data etc:

image
image620×700 51.6 KB

No output.

Without the 'where' clause:

image
image505×1213 56.1 KB

6

The query is working then. The firstname field doesn't have a tom - it has Tim, Michael, and Jonathan. If you change you where clause to where firstname = 'Tim' it should return a single record.

7

Alas 'tis not that simple. In the screenshots above the dataset has changed, as has the filter criteria...

8

I think you may want to transform the initial data you are querying on because each key is an array - so as to have a better access to the field itself...using a map function...

9

Now there's an idea, any chance of a generic example?

Apologies but I have to. put a pin in this until Monday - don't think I'm rude when I don't respond until then...

Thanks Scott.

10

Similar to this format: (possibly)

{{contact_query.data.map(i=>i.last_name)}}

11

Hi and I hope this week is a good one for you.

Unfortunately 'select * from {{ contact_query.data.map(i=>i.lastname) }}' results in 'TypeError: contact_query.data.map is not a function' (lastname does not have an underscore divider).

This is doubly frustrating because my efforts so far are based on another community post...

12

OK, here's my Heath Robinson solution.
I want to automatically populate a text area with the item from an array that meets a criteria.

  1. Use the data to populate a table
  2. Set the Default Filter on the table to reflect the criteria
  3. Set the text area to display the relevant columns from the table
  4. Hide the table

Not elegant but it works (can't waste any more time on working out how to do it the 'right' way, the clock is ticking).

Many thanks - as always.

13

What I meant was for you to first transform the returned data and then use that result in your query.... sorry for not explaining that part of it...

14

No worries, I did attempt that for about 90 mins until I had a brainwave and implemented my inelegant workaround. For all practical purposes my problem is solved. It would have been nice to use the facilities as intended but I'm happy to stage a tactical retreat from this skirmish in the interests of continuing the wider campaign (i.e. hitting deadlines and getting paid).

15

if you're curious I believe the problem is with how the data you're working on is organized for 'where' to work. SQL queries are returned in columnar format, which isn't what WHERE is expecting.

what were given:

  • a single RECORD (we're using SQL so it's being consumed as a Record not an object) contact_query.data with 21+ properties all of type array with identical lengths and presumably identical sorting (ie: index 1 of lastname matches index 1 of firstname and so on)

what we're trying to do:

  • use the SQL 'WHERE' clause to select all items related to a lastname

what you're currently trying:

  • select * from {{ contact_query.data }} where lastname='Smith'
  • broken down:
    • select * from (1 Record) where lastname='Smith'
    • select ALL from (1 Record) where lastname='Smith'
    • select ALL from (1 Record) where ['Smith','Lastname1', 'Lastname2'] = 'Smith'
      and that comparison is why it doesn't return anything. and if it did, it wouldn't have a Record to use Select * on, it would have a string or an index.

Solution ( use formatDataAsArray):
first we need to generate a separate object/record for each 'index'. so using index 0 from every property, we make obj0 and from using index1 we make another and so on. our goal is to go from:

//this is columnar format
{
lastname:['ln0', 'ln1', 'ln2'],
firstname:['fn0', 'fn1', fn2']
}

to

//json object format
{
  lastname: ln0,
  firstname: fn0
},
{
  lastname: ln1,
  firstname: fn1
},
{
  lastname: ln2,
  firstname: fn2
}

Retool provides 2 types of functions to help w SQL: formatDataAsArray() and formatDataAsObject(). for WHERE we need an array, so we use {{ formatDataAsArray(contact_query.data) }}

(TLDR;)
change

select * from {{ contact_query.data }}
where lastname='Smith'

to

select * from {{ formatDataAsArray(contact_query.data) }}
where UPPER(lastname) = UPPER('Smith')

note: I added upper so that the search is not case sensitive. otherwise a lastname of 'smith' while searching for 'Smith' won't be found.

1 Like
16

Thank you so much for your patience and diligence in giving an explanation as well as the solution.

As you can see, it works like a charm:

image