Weird JSON document structure

SQL++
,
1

I’m going crazy trying to query this object:

{
“prof_id”: “6505”,
“publications”: {
“0”: {
“authors”: [
{
“auth_id”: “5580”,
“first_name”: “Jon”
},
{
“auth_id”: “5400”,
“first_name”: “Jane”
}
],
“pu_id”: “85016”,
“title”: “Theoretical models of math”,
“type”: “Journal”
},
“1”: {
…
},
“7”: {
…
}
}
}

The problem is that I can’t access to the fields inside the single publications, because they are objects with a random number as a name, not arrays. So if, for instance, I would like to use “auth_id”, I have no idea how to do that.
Could someone help me out, please?

2

Use OBJECT_PAIRS(publications) it coverts to ARRAY of name, val pairs and then you can use array construct on val to access required fields. Also you can take look OBJECT_VALUES(),OBJECT_NAMES(),…

Example:
SELECT OBJECT_VALUES(publications) FROM default;
SELECT META().id FROM default WHERE ANY v WITHIN OBJECT_VALUES(publications) SATISFIES v.auth_id = "5400" END;
SELECT META().id FROM default WHERE ANY v WITHIN publications SATISFIES v.auth_id = "5400" END;
1 Like
3

Thank you, that’s exactly what I was looking for!