Query JSON with SQL returns nothing

💬 App Building
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