How do we loop through the data retuned by a query and define conditions based on the returned value.
For example we have cursors in tsql to loop through the values returned from a query.
And also want to know how we deal with variables declaration and assignment in n1ql
N1QL has variables and loops for ARRAYs you can check documentation. Query results are considered as ARRAY and you can loop through or you can use SDKS.
https://blog.couchbase.com/working-json-arrays-n1ql/
If still has questions post the query and what you want achieve.
I have checked that link, that has information about working with arrays. I have provided a sample of the scenario in tsql that I am trying to achieve with n1ql.
declare @table as table (
ID int,
name varchar(15)
)
insert into @table values (1,‘Dany’)
insert into @table values (2,‘John’)
insert into @table values (3,‘Peter’)
insert into @table values (4,‘falcon’)
declare @ID int
declare @curs as cursor
set @curs = cursor fast_forward for
select ID from @table
open @curs
fetch next from @curs into @ID
while(@@FETCH_STATUS=0)
begin
Declare @name varchar(15) = (select name from @table where ID = @ID)
print 'Printing name of ID : ’ + convert(varchar(2),@ID)
print @name
fetch next from @curs into @ID
end
close @curs
deallocate @curs
No Cursors in the N1QL , Application with named/positional parameter can be achieved this.
Above query can get results without cursors as follows.
SELECT ID, ARRAY_AGG(name)
FROM table
WHERE ....
GROUP BY ID;I am trying to collect the data metadata for the all the documents and frame them tsql to load the data into mssql…I am able to acheive this but, it is taking too long for 40K rows to process.
looking for any better option that completes with in seconds…
import sys
import os
import subprocess
import requests
import json
import pyodbc
from requests.auth import HTTPBasicAuth
from couchbase.cluster import Cluster
from couchbase.cluster import PasswordAuthenticator
HostName = ‘xxxx’
UserName = ‘xxxx’
Password = ‘xxxx’
SQLServer = ‘xxxx’
SQLdb = ‘xxxx’
SQLlogin = ‘xxxx’
SQLpassword = ‘xxxxx’
##Couchbase function
def couchbase_conn (HostName,BucketName,UserName,Password) :
cluster = Cluster(‘couchbase://%s:8091’%(HostName))
authenticator = PasswordAuthenticator(UserName,Password)
cluster.authenticate(authenticator)
Bucket = cluster.open_bucket(BucketName)
Query = “”
Output = “”
fh=open(“output.sql”,“w+”)
for row in Bucket.n1ql_query(‘SELECT distinct META().id FROM %s‘%(BucketName)):
print (’----------------’)
print (“Document : %s”%(row[‘id’]))
print (’----------------’)
ID = row[‘id’]
#q = “SELECT ARRAY_DISTINCT(ARRAY v[0] FOR v IN PAIRS(d) END)FROM %s AS d WHERE META().id = ‘%s’”%(ID)
#print (q)
for row in Bucket.n1ql_query(“SELECT ARRAY_DISTINCT(ARRAY v[0] FOR v IN PAIRS(d) END)FROM %s AS d WHERE META().id = ‘%s’”%(BucketName,ID)):
print (“Attributes Array : %s”%(row[’$1’]))
print (’----------------’)
listdata = row[’$1’]
#print (type(listdata))
for index in range(len(listdata)):
#print (‘see the list’)
print (listdata[index])
Query = “INSERT INTO xxxx VALUES (’%s’,’%s’,’%s’,’%s’,NULL) ; “%(HostName,BucketName,ID,listdata[index])
fh.write(”%s”%Query)
print (’’)
#print (Query)
print (’’)
print (’*******************’)
print (’’)
print (’’)
print (’’)
fh.close()
Bucket.closed
#print (cb)
return Query
BucketName = ‘xxxx’
Output = couchbase_conn (HostName,BucketName,UserName,Password)
Does this work?
SELECT ARRAY_DISTINCT(ARRAY v[0] FOR v IN PAIRS(d) END)FROM bucketname AS d WHERE META().id in (SELECT distinct META().id FROM bucketname)
Basically you want get field names. JSON can have nested objects and arrays, array of objects etc… There is no easy way to get the paths for all possible combinations. The PAIRS() only gives current level field name not path (nested object, array).
You don’t need subquery. Equivalent query is
SELECT ARRAY_DISTINCT(ARRAY v[0] FOR v IN PAIRS(d) END)FROM bucketname AS d WHERE META().id IS NOT NULL;
If you need field names on top level
SELECT OBJECT_NAMES(d) FROM bucketname AS d WHERE META().id IS NOT NULL;