Display Array of objects as single record/ row

SQL++
,
1

Hi I’m new to N1QL, can anyone help me with select query.
Below is the document structure.

{
"id" : "0123",
"arr1" : [
{
"name" : "phone",
"value" : "9999999999"
},
{
"name" : "email",
"value" : "cb@gmail.com"
}
] 
}

Expected Result :
[
{
“id” : “0123”,
“phoneNo” : “9999999999”,
“emailaddress” : “cb@gmail.com”
}
]

Thanks

2

You can try:

UPDATE default d
SET d = object_concat(object_remove(d,"arr1"),OBJECT v.name:v.`value` FOR v IN d.arr1 END)
WHERE d.arr1 IS VALUED
...

HTH.

3

Also use this.

UPDATE default AS d
SET d = OBJECT_ADD(OBJECT v.name:v.`value` FOR v IN d.arr1 END,"id",d.id)
WHERE .....
4

Apologies for the typo, wanted to retrieve(select query )the data

5 
SELECT d.id, OBJECT v.name:v.`value` FOR v IN d.arr1 END.*
FROM default AS d
WHERE ....
6

Thanks @vsr1 , query is working fine.

What if we have dynamic fields inside the objects?
For example


{
"id" : "0123",
"arr1" : [
{
"phone" : "9999999999",
"email" : "cb@gmail.com",
"type" : "PRIMARY"
},
{
"phone" : "9999988888",
"email" : "cb_1@gmail.com",
"type" : "SECONDARY"
}
] 
}

EXPECTED OUTPUT :
[
{
“id” : “0123”,
“primaryPhone” : “9999999999”,
“primaryEmailAddress” : “cb@gmail.com",
“secondaryPhone” : “9999988888”,
“secondaryEmailAddress” : “cb_1@gmail.com",
}
]

7 
SELECT d.id, OBJECT IFMISSINGORNULL(TO_STR(v.type),"")||v.name:v.`value` FOR v IN d.arr1 END.*
FROM default AS d
WHERE ....
8

This is not working , i guess since name and value fields are not present in the document the above query is just returning id .

10 
SELECT d.id,
OBJECT v.name:v.val FOR v IN d1 END.*
FROM default AS d
LET d1 = ARRAY_FLATTEN(ARRAY (ARRAY {"name":IFMISSINGORNULL(TO_STR(v.type),"")||f1, "val":v1} FOR f1:v1 IN v WHEN f1 != "type" END) FOR v IN d.arr1 END,1);

 {
        "id": "0123",
        "PRIMARYemail": "cb@gmail.com",
        "PRIMARYphone": "9999999999",
        "SECONDARYemail": "cb_1@gmail.com",
        "SECONDARYphone": "9999988888"
    }

WITH obj AS ( {"phone":"Phone", "email":"EmailAddress", "PRIMARY":"primary", "SECONDARY":"secondary"})
SELECT d.id,
OBJECT v.name:v.val FOR v IN d1 END.*
FROM docs AS d
LET d1 = ARRAY_FLATTEN(ARRAY (ARRAY {"name":IFMISSINGORNULL(obj.[v.type],"")||obj.[f1], "val":v1} FOR f1:v1 IN v WHEN f1 != "type" END) FOR v IN d.arr1 END,1);

{
        "id": "0123",
        "primaryEmailAddress": "cb@gmail.com",
        "primaryPhone": "9999999999",
        "secondaryEmailAddress": "cb_1@gmail.com",
        "secondaryPhone": "9999988888"
    }
11

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.