Join document from many reference

N1QL
1

Dear All,

Assume i have some master reference document like this:

{“id”:“product1”,“type”:“product”,“name”:“Book”}
…

{“id”:“category4”,“type”:“category”,“name”:“Stationery”}
…

{“id”:“region2”,“type”:“region”,“name”:“Alaska”}
…

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
}

please help. thanks
best regards,

rizal

2 
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";
3

thanks man, you really great. it seems i have to use the first solution, because i want to define each of the field first. thanks again

4

one more thing friend, how about if the value from reference is not exist, but i still want to show the field with value “”

ex:
{“id”:“trx1”,“type”:“trx”,“region”:“”,“product”:“product1”,“category”:“category4”,“qty”:5}

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

best regards,

rizal

5

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.

6

great man, i missed in ifmissing and ifmissingornull function. thanks for the response

1 Like