Selecting the name of the json or array object

SQL++
,
1

Here is a sample document
{
“vendoraccommodationroomkey”: “76FD9D6E-90D1-E911-80F0-005056B91133”,
“resortgroupid”: 14247,
“supplierid”: 12,
“roomname”: “Pool View: King room”,
“isnonrefundable”: 0,
“isbreakfastincluded”: 0,
“accommodation_category”: “standard”,
“accommodation_type”: “room”,
“bed_types”: [
{
“king”: 1
}
],
“number_of_bedrooms”: null,
“view”: true,
“orientation”: {
“view”: [
“pool”
]
}
}

I’d like to create a string out of this data by combining some of the elements (columns?). For example, I’d want to create an output that looks like the below using concatenation features in the select:

standard room 1 king bed pool view

I’m stuck on the bed part, as the name of the bed is also the actual object name. Is it possible, knowing that that attribute could change, that i am able to select it no matter what is there? I’ve gotten this far, but I can’t seem to isolate the attribute in a way that allows me to concatenate it in the select

select r.accommodation_category||’ '||r.accommodation_type, x.*
from roomstest r
unnest bed_types as x

2

Use OBJECT_PAIRS() convert dynamic object into ARRAY and then use that to get field name and value. https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/objectfun.html#object_pairsexpression

INSERT INTO default VALUES (“k01”,{ “vendoraccommodationroomkey”: “76FD9D6E-90D1-E911-80F0-005056B91133”, “resortgroupid”: 14247, “supplierid”: 12, “roomname”: “Pool View: King room”, “isnonrefundable”: 0, “isbreakfastincluded”: 0, “accommodation_category”: “standard”, “accommodation_type”: “room”, “bed_types”: [ { “king”: 1, “queen”: 2 }, { “twin”: 1 } ], “number_of_bedrooms”: null, “view”: true, “orientation”: { “view”: [ “pool”, “hill” ] } });

SELECT CONCAT(r.accommodation_category, " ", r.accommodation_type, " ", bt," ", vs, " view")
FROM default AS r
LET bt = REPLACE(TRIM(ENCODE_JSON(ARRAY REPLACE(TRIM(ENCODE_JSON(ARRAY CONCAT(TOSTRING(v.val), " ", v.name, " bed") FOR v IN OBJECT_PAIRS(b) END),"[]\""), "\",\"", ",") FOR b IN r.bed_types END),"[]\""), "\",\"", ","),
     vs = REPLACE(TRIM(ENCODE_JSON(r.orientation.`view`), "[]\""), "\",\"", ",");

"standard room 1 king bed,2 queen bed,1 twin bed pool,hill view"

In 6.50 CONCAT2 () function simplifies few things. MB-31737

SELECT CONCAT(r.accommodation_category, " ", r.accommodation_type, " with ", bt, " and ", vs)
FROM default AS r
LET bt = CONCAT ("[", REPLACE(TRIM(ENCODE_JSON(ARRAY REPLACE(TRIM(ENCODE_JSON(ARRAY CONCAT(TOSTRING(v.val), " ", v.name) FOR v IN OBJECT_PAIRS(b) END),"[]\""), "\",\"", ",") FOR b IN r.bed_types END),"[]\""), "\",\"", ","), "] beds"),
     vs = CONCAT("[", REPLACE(TRIM(ENCODE_JSON(r.orientation.`view`), "[]\""), "\",\"", ","), "] view");

"standard room with [1 king,2 queen,1 twin] beds and [pool,hill] view"
3

Thanks for the help, I’m going to try to unpack your answer and see if I have any questions. Theres alot going on there :slight_smile: