How to concat a field and a value of a subquery

SQL++
1

Looking for a way to create a query that gets data from multiple query’s

The query currently uses a sub query to get the count as i need to get this from mt farm docs. All that works as expected but i would like to be to return in the name

“name”: “Aegean Heights - (96)”

The question is how can i reference the result of the subquery in the name field?

Below is the current query

SELECT META().id value_key,
       (
           SELECT RAW COUNT(*)
           FROM Contacts d
           WHERE d._type = 'farm'
               AND d.Record.tract IN c.tract_id)[0] AS count_nbr,
       name
FROM Contacts c
WHERE _type = "tract_info"
ORDER BY name

My current query returns data like this

  {
    "count_nbr": 96,
    "name": "Aegean Heights",
    "value_key": "tract_info::35807DA0-7881-421C-81CF-F0AC1F99E5F0"
  },
2 
SELECT META().id value_key,
       name || " - "  || TO_STR((
           SELECT RAW COUNT(*)
           FROM Contacts d
           WHERE d._type = 'farm'
               AND d.Record.tract IN c.tract_id)[0] ) AS name
FROM Contacts c
WHERE _type = "tract_info"
ORDER BY name
3

Thanks so one has to convert the result to a String and then you can concat via standard || i tried the concat without the TO_STR and failed.

1 Like