How to remove array wrapper from mariadb server

user_15796895 0
Viewed 28

I want to remove the array wrapper surrounding a query result as I'm running a for loop to push the object into an array. This is my query

"SELECT * FROM  jobs WHERE id = ? FOR JSON PATH, WITHOUT_ARRAY_WRAPPER"

but I'm getting this result in postman

{
    "status": "Failed",
    "message": "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'JSON PATH, WITHOUT_ARRAY_WRAPPER' at line 1"
}
mariadb sql
1 Answers

for json path is a feature of Microsoft SQL Server. There is a standard for JSON in SQL, but don't expect most SQL servers to follow it.

You can get a single JSON object for each row with json_object.

-- {"id": 2, "name": "Bar"}
select
  json_object('id', id, 'name', name)
from jobs 
where id = 2

Rather than query each job individually and then appending to an array, you can do this in single query using the in operator to query all desired rows at once, and then json_arrayagg to aggregate them into a single array.

-- [{"id": 1, "name": "Foo"},{"id": 3, "name": "Baz"}]
select
  json_arrayagg( json_object('id', id, 'name', name) )
from jobs 
where id in (1, 3)

This is much more efficient. In general, if you're querying SQL in loops there's a better way.

Demonstration.

user_146600
Related