Double quotes problem

Queries and Resources
1

I have read through a number of posts about problems with strings, quotes and SQL queries but I think my problem is simpler and the solutions I have read do not apply exactly.

I have an SQL query returning a nvarchar(max) column from SQL. The values in this column are a comma separated list of floats but to SQL and the query it is just a big string. I am passing it to a REST API that expects a JSON body of the form:

  {
    "vectors": [
      {
        "id": "some_guid",
        "metadata": {"model": "some_string"},
        "values": [ array of floats ]
      }
    ],
    "namespace": "foo"
  }

The problem is here: "values": [ array of floats ]

This needs to turn into something like this:

"values": [0.006512424,-0.005773521,0.025028666,-0.006938973]

The value coming from SQL is just the string "0.006512424,-0.005773521,0.025028666,-0.006938973" so I have to wrap it in [ and ] but no matter how I do that I always end up with this hitting the API:

"values": "[0.006512424,-0.005773521,0.025028666,-0.006938973]"

And then the API call fails.

I have tried things like {{foo}}.replace('"','') but no matter what the resulting string ends up being wrapped in double quotes.

2

Hello,

can you try to use the split() and map() like below?

image
image1279×493 35.5 KB

3

This my first try:

image
image1392×509 44.5 KB

4

For some background here is what it looks like in the "before" case:

image
image1504×534 65.5 KB

5

Hi @Roland_Alden

Is vector an array or string?

The split() is a function splitting comma separated string into array. Based on the what you wrote, you want to take a SQL value of string. However, on your last image most of other values seems to be array since you filter to [0] index.

6

Not sure you can trust screen shots since they are the output of pretty printers that can add and format to make things nice to read. Doesn't mean that's what is really there.

azure_fingerprints_read is a "retool normal" SQL query. vectors is a nvarchar(MAX). What it is in concrete terms is a string consisting of a sequence of comma separated real numbers.

What I need to produce for the benefit of the REST API is a bit of json where an array of real numbers has a certain representation defined by json.

Of course if I take "vectors" and wrap it in [ ] and stick it in Postman it's going to work. But how to get Retool to stop inserting " where it is not wanted?

7

Whew! Made it work:

  {
    "vectors": [
      {
        "id": "{{azure_fingerprints_read.data['fingerprint-guid'][0].toLowerCase()}}",
        "metadata": {"model": "{{azure_fingerprints_read.data.model[0]}}"},
        "values": [{{azure_fingerprints_read.data.vector[0].split(",").map(i => i*1)}}]
      }
    ],
    "namespace": "tenant-one"
  }

Thank you!

1 Like