CSV upload & N1QL ETL

I’m simulating importing two CSV files … one with user profile data … second the table of friends connected via foreignKey.

55%20PM

33%20PM

I created two indexes one on ID and the other on foreignKey

I’m able to get the JOINED result below.

But what I want is to NEST the result so thats its looks like the below.

{
"name":"bob",
"friends":[
           {"friendsEmail":"ted@ted.com"},
           {"friendsEmail":"nick@nick.com"}
          ]
}

and then from there I want to insert the above as a document.

The idea is that I import a CSV and then use N1QL and/or Eventing/Function to do ETL and insert.

SELECT A.name, ARRAY_AGG({"firendsEmail":B.friend}) AS friends 
FROM cbprofie AS A JOIN cbprofie AS B ON A.id = B.foreignKey
GROUP BY A.name;

You can also use ANSI NEST Check Example 17 in https://blog.couchbase.com/ansi-join-support-n1ql/

2 Likes

@vsr1,

1 Like