Creating an array of objects and dynamically assigning the object value

SQL++
, , ,
1

Hello
I’m trying to build a query that would return an object. The keys in this object would be each user’s primary key and the values would be the user’s data.
I want it to look like this:

{
	"user::1": {
		fullName: {
			first: "User",
			last: "one"
		},
		profilePicture: "www.google.com"
	},
	"user::2": {
		fullName: {
			first: "User",
			last: "Two"
		},
		profilePicture: "www.google.com"
	},
	"user::3": {
		fullName: {
			first: "User",
			last: "Three"
		},
		profilePicture: "www.google.com"
	}
}

Here is the query that I was able to come up with:

SELECT RAW OBJECT META(u).id: {'fullName': u.fullName, 'profilePicture': u.image} FOR `user` IN [ `u` ] END

But for some reason each object gets wrapped inside another object like this:

[
	{
		"user::1": {
			fullName: {
				first: "User",
				last: "One"
			},
			profilePicture: "www.google.com"
		},
	},
	{
		"user::2": {
			fullName: {
				first: "User",
				last: "Two"
			},
			profilePicture: "www.google.com"
		},
	},
	{
		"user::3": {
			fullName: {
				first: "User",
				last: "Three"
			},
			profilePicture: "www.google.com"
		},
	},
]

I want to get rid of the outer object wrapping each returned document. I tried many different options, such as user RAW above but none of them seem to work.

2 
SELECT RAW OBJECT v.id : {'fullName': v.fullName, 'profilePicture': v.image}
           FOR v IN (SELECT META(u).id, u.fullName, u.image
                     FROM mybucket AS u
                     WHERE ..........) END;
2 Likes
3

Hey thanks a lot @vsr1. I got one more question:
putting the AS u after the FROM mybucket didn’t work for me so I moved it to after:

SELECT RAW OBJECT v.id : {'fullName': v.fullName, 'profileImage': v.image} FOR v IN (
    SELECT META(u).id,
           u.fullName,
           u.image AS u
    FROM mybucket

Now I’m trying to add a case for when a user doesn’t have a profile picture
How can I do that in this query?

SELECT RAW OBJECT v.id : {'fullName': v.fullName, 'profileImage': v.image} FOR v IN (
    SELECT META(u).id,
           u.fullName,
           CASE WHEN u.image THEN u.image ELSE '' END AS u
    FROM mybucket
4

@yosef,

SELECT RAW OBJECT v.id : {v.fullName,  v.profileImage}
           FOR v IN (SELECT META(u).id, u.fullName, IFMISSINGORNULL(u.image,"") AS profileImage
                     FROM mybucket AS u
                     WHERE ..........) END;
1 Like