Problem for query to retrieve null value

SQL++
1

Hi,
i have a problem with a query , the query is

SELECT ARRAY dim.name FOR dim IN ARRAY_SORT(d) when dim.name in [‘NAME’,‘SURNAME’,‘ADDRESS’,‘city’,‘cap’] END AS titles,
ARRAY dim.value FOR dim IN ARRAY_SORT(d) when dim.name in [‘NAME’,‘SURNAME’,‘ADDRESS’,‘city’,‘cap’] END as values
FROM test as f NEST test as d ON KEYS ARRAY dim FOR dim in f.properties END
WHERE f._class = ‘class’ AND f.name in [ ‘city’,‘cap’]

the documents that i use are in this format

{
“name”: “city”,
"_class": “class”,
“value”: “city1”,
“properties”: [
“4fc8c2d9-1c7a-4b41-bf94-26664e73950b”,
“a06b599e-0221-4ef3-9229-7703341b0299”,
“6ec33bf7-f687-499c-aefa-e05634b970dd”,
“16831c7d-9d9d-4188-a4df-a86496ae4949”
]
}

{
“name”: “cap”,
"_class": “class”,
“value”: “cap1”,
“properties”: [
“4fc8c2d9-1c7a-4b41-bf94-26664e73950b”,
“a06b599e-0221-4ef3-9229-7703341b0299”,
“6ec33bf7-f687-499c-aefa-e05634b970dd”,
“16831c7d-9d9d-4188-a4df-a86496ae4949”
]
}

the values on properties are the key for other documents with this format

{
“name”: “ADDRESS”,
“value”: “street example”
}

My queries return this

[
{
“titles”: [
“ADDRESS”,
“SURNAME”,
“cap”
],
“values”: [
“street example”,
“testSurname”,
“cap1”
]
},
{
“titles”: [
“ADDRESS”,
“NAME”,
“cap”
],
“values”: [
“street example”,
“test name”,
“cap1”
]
}
]

but i want a result of thi type

[
{
“titles”: [
“ADDRESS”,
“NAME”,
“SURNAME”,
“city”,
“cap”
],
“values”: [
“street example”,
null ,
“testSurname”,
“city1” ,
null
]
},
{
“titles”: [
“ADDRESS”,
“NAME”,
“SURNAME”,
“city”,
“cap”
],
“values”: [
“street example”,
“test name”,
null,
null,
“cap1”
]
}
]

Is there anybody who can help me?

2 
INSERT INTO default VALUES ("k01",{ "name": "city", "_class": "class", "value": "city1", "properties": ["addr1","name1","cap1"] }),
                    VALUES ("k02",{ "name": "cap", "_class": "class", "value": "cap1", "properties": ["addr1","cap1"] }),
                    VALUES ("addr1",{ "name": "ADDRESS", "value": "street example" }),
                    VALUES ("name1",{ "name": "NAME", "value": "name example" }),
                    VALUES ("cap1",{ "name": "CAP", "value": "cap example" });

SELECT  nd[*].name AS titles, nd[*].`value` AS `values`
     FROM (SELECT RAW (SELECT RAW d
                       FROM default AS d USE KEYS f.properties
                      )
           FROM default AS f
           WHERE  f._class = "class" AND f.name IN ["city","cap"]) AS d
       LET nd = ARRAY CASE WHEN v NOT IN d[*].name
                           THEN {"name":v,"value":null}
                           ELSE (FIRST v1 FOR v1 IN d WHEN v1.name = v END) END
                FOR v IN ["NAME","SURNAME","ADDRESS","CITY","CAP"] END ;

SELECT  nd[*].name AS titles, nd[*].`value` AS `values`
 FROM (SELECT RAW d
       FROM default AS f NEST default d ON KEYS f.properties
       WHERE  f._class = "class" AND f.name IN ["city","cap"]) AS d
   LET nd = ARRAY CASE WHEN v NOT IN d[*].name
                       THEN {"name":v,"value":null}
                       ELSE (FIRST v1 FOR v1 IN d WHEN v1.name = v END) END
            FOR v IN ["NAME","SURNAME","ADDRESS","CITY","CAP"] END ;

If you need to change order in the array change [“NAME”,“SURNAME”,“ADDRESS”,“CITY”,“CAP”] required order.

1 Like
3

Thanks, work perfectly