building url query string using n1ql

Question

using couchbase 5

I need to build a query string from this object

[
  {
    "_id": 190,
    "querystring": [
      {
        "name": "p1",
        "value": "val1"
      },
      {
        "name": "p2",
        "value": "val2"
      }
    ]
  }
]

the expected output should be

p1=val1&p2=val2

can anyone help here?

after few attempts I think I got closer to the solution I need.

[
  {
    "_id": 190,
    "res": [
      "company_id=$PREFIJO&",
      "user_country=$COUNTRY&",
      "offer_unique_code=$PIXEL&",
      "pub_id=$PUBID&"
    ]
  }
]

now, how can I convert "res" to a concatenated string of all the array elements?

Answer 1

WITH obj AS ({ "_id": 190, "querystring": [ { "name": "p1", "value": "val1" }, { "name": "p2", "value": "val2" } ] })
SELECT obj._id, CONCAT2("&", ARRAY CONCAT2("=",v.name,v.`value`) FOR v IN obj.querystring END) AS res;

Array of objects

WITH objs AS ([{ "_id": 190, "querystring": [ { "name": "p1", "value": "val1" }, { "name": "p2", "value": "val2" } ] },
              { "_id": 191, "querystring": [ { "name": "p3", "value": "val1" }, { "name": "p4", "value": "val2" } ] }
             ])
SELECT obj._id, CONCAT2("&", ARRAY CONCAT2("=",v.name,v.`value`) FOR v IN obj.querystring END) AS res FROM objs AS obj ;

Older version where CONCAT2() not available, get array of strings (name=val) and do in application or use the following technique. Assume your name/val doesn't have any replace characters.

WITH objs AS ([{ "_id": 190, "querystring": [ { "name": "p1", "value": "val1" }, { "name": "p2", "value": "val2" } ] },
              { "_id": 191, "querystring": [ { "name": "p3", "value": "val1" }, { "name": "p4", "value": "val2" } ] }
             ])
SELECT obj._id, replace(replace(replace(encode_json(ARRAY CONCAT(v.name,"=",v.`value`) FOR v IN obj.querystring END),"\",\"","&"),"[\"",""),"\"]","") AS res FROM objs AS obj ;

If single document then have ARRAY of objects then use UNNEST

If there is number , convert to string using TO_STR() before CONCAT operation

https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/stringfun.html#fn-str-concat2