How to query an array in a json datasource

Good afternoon,

new to json queries I would like to be able to get both longitude and latitude from an array.
My json source is

[{
"__system":{
		"status":null,
		"submitterId":"14"
	},
	"__id":"uuid:2d9eb658-82ef-4e2d-bf89-90c4355ebeb8",
	"meta":{
		"instanceID":"uuid:2d9eb658-82ef-4e2d-bf89-90c4355ebeb8",
		"instanceName":"cen_languedoc"
	},
		"nombre_utilisateurs":20,
		"repondant":"Mathieu Bossaert",
		"email":"me@maydomain.com",
		"structure":"CEN L-R",
		"location":{
			"type":"Point",
			"properties":{"accuracy":0},
			"coordinates":[4.3565987,43.455454,0]
		}
}]

My query is written like this :

url: https://my_server.mydomain.fr/v1/projects/2/forms/usagers_odk.svc/Submissions
fields: [email, structure, nombre_utilisateurs, location.coordinates]
path: value

Here is the result :

email structure nombre_utilisateurs location.coordinates
me@maydomain.com CEN LR 20 [4.3565987,43.455454,0, 0]

What would be the syntax to get separately the two first columns of the location.coordinates array ?
Thanks a lot !

I don’t think the JSON data source can do this on its own. You can use QRDS for it though. For example:

SELECT *,
       json_extract(location_coordinates, '$[0]') latitude,
       json_extract(location_coordinates, '$[1]') longitude
FROM cached_query_<query id from your query>

I just tested it and it worked.

2 Likes

thanks @k4s1m,

I did it as a workaround and yes it works !

1 Like