Now i have transaction document like:
{“id”:“trx1”,“type”:“trx”,“region”:“region2”,“product”:“product1”,“category”:“category4”,“qty”:5}
how do i make the n1ql, so the result become:
{“id”:“trx1”,
“type”:“trx”,
“region_name”:“Alaska”,
“product_name”:“Book”,
“category_name”:“Stationery”
“qty”:5
}
SELECT
d.type,
d.id,
d.qty,
(SELECT RAW p.name FROM default AS p USE KEYS d.product)[0] AS product_name,
(SELECT RAW p.name FROM default AS p USE KEYS d.region)[0] AS region_name,
(SELECT RAW p.name FROM default AS p USE KEYS d.category)[0] AS category_name
FROM default d
WHERE type = "trx";
Generalized one. Add the fields you want inside OBJECT_VALUES
SELECT
d.type,
d.id,
d.qty,
(OBJECT v.type||"_name": v.name FOR v IN
(SELECT p.name, p.type FROM default AS p USE KEYS OBJECT_VALUES({d.product, d.region, d.category})) END).*
FROM default d
WHERE type = "trx";
assume the region is empty, but i still want to show the result like:
{“id”:“trx1”,
“type”:“trx”,
“region_name”:“”,
“product_name”:“Book”,
“category_name”:“Stationery”
“qty”:5
}
i already try put ifnull, but it not working, the result become field region_name gone. i try from your first solution. is the any way so i can still show the region_name even when the reference not exist?. thanks
IFMISSING((SELECT RAW p.name FROM default AS p USE KEYS d.product)[0],"")
IFMISSINGORNULL((SELECT RAW p.name FROM default AS p USE KEYS d.product)[0],"")
In JSON fields can appear any where in object. In pretty format it sorts alphabetically.