N1QL Access different indices of Array in JSON


#1

I have a bucket of JSON documents. They are heterogenous documents with different items. Each document is an array with different objects. I need to select all objects of the array that satisfied certain criteria. I realize that you defined “OVER” keyword to iterate the collections. But I am not able to use it in my exercise and I only get the results when I specifically define the index.

SELECT *
FROM newtest_bucket.array[4] As g Where myfield IS Not MISSING
If I dont define [4], I will not get any results. Even when I try “OVER”

Really appreciate your comments.

Thanks


#2

Hello Neda,

Please post the results of the following:

SELECT VALUE() FROM newest_bucket LIMIT 5


#3

Thanks for the response. Actually my JSON files are pretty large. It is impossible to have 5 of them posted here. Below is one of them.

{ “@graph” : [ {
"@id" : “http://chcs.taps.com/2-245412”,
“sponsor_name-2” : “http://chcs.taps.com/2-245412”,
“rdf:type” : {
"@id" : “j.0:2”
}
}, {
"@id" : “http://chcs.taps.com/52”,
“rdf:type” : {
"@id" : “owl:Class”
},
“rdfs:label” : {
"@language" : “en”,
"@value" : “Presciption”
}
}, {
"@id" : “j.2:-1000791”,
“j.0:child_resistant_cont-52” : {
"@type" : “xsd:boolean”,
"@value" : “true”
},
“j.0:comments-52” : “NONE”,
“j.0:date_time_received_from_oe-52” : {
"@type" : “xsd:dateTime”,
"@value" : “1996-10-22T08:02:00Z”
},
“j.0:days_supply-52” : “1”,
“j.0:drug-52” : “ACETAM_CODEINE #3 (300_30MG) TAB”,
“drug-52” : “http://chcs.taps.com/50-567”,
“j.0:expanded_sig-52” : “TAKE 1 TO 2 TABLETS EVERY~4-6 HOURS AS NEEDED FOR~PAIN ~”,
“j.0:expiration_date-52” : {
"@type" : “xsd:dateTime”,
"@value" : “1996-10-23T00:00:00Z”
},
“j.0:fill_expiration-52” : {
"@type" : “xsd:dateTime”,
"@value" : “1996-10-23T00:00:00Z”
},
“j.0:label_width-52” : “27”,
“last_dispensing_pharmacy-52” : “http://chcs.taps.com/59_2-1”,
“j.0:last_fill_date-52” : {
"@type" : “xsd:dateTime”,
"@value" : “1996-10-22T00:00:00Z”
},
“j.0:last_label_print_date-52” : {
"@type" : “xsd:dateTime”,
"@value" : “1996-10-22T00:00:00Z”
},
“logged_by-52” : “http://chcs.taps.com/3-9997”,
“j.0:login_date-52” : {
"@type" : “xsd:dateTime”,
"@value" : “1996-10-22T08:02:00Z”
},
“meprs_code-52” : “http://chcs.taps.com/8119-379”,
“j.0:meprs_code-52” : “AAAA”,
“mtf_division-52” : “http://chcs.taps.com/40_8-1”,
“j.0:order_date_time-52” : {
"@type" : “xsd:dateTime”,
"@value" : “1996-10-22T08:15:00Z”
},
“j.0:order_entry_number-52” : “961022-00393”,
“order_pointer-52” : “http://chcs.taps.com/101-1890533”,
“outpatient_site-52” : “http://chcs.taps.com/59_2-1”,
“patient-52” : “http://chcs.taps.com/2-245412”,
“provider-52” : “http://chcs.taps.com/6-11332”,
“j.0:provider-52” : “KEYSXXX,CHESTNUT”,
“j.0:qty-52” : “15”,
“j.0:refills-52” : “0”,
“j.0:refills_remaining-52” : “0”,
“j.0:rx_-52” : “H30021093”,
“j.0:sig-52” : “TAKE 1-2 TABLETS Q4-6H PP #15”,
“status-52” : “j.1:__11_E-DISCONTINUED”,
“rdf:type” : [ {
"@id" : “j.0:52”
}, {
"@id" : “owl:NamedIndividual”
}, {
"@id" : “http://chcs.taps.com/52
} ,
“rdfs:label” : “H30021093”
}, {
"@id" : “j.2:-1000792”,
“j.0:child_resistant_cont-52” : {
"@type" : “xsd:boolean”,
"@value" : “true”
},
“j.0:comments-52” : “NONE”,
“j.0:date_time_received_from_oe-52” : {
"@type" : “xsd:dateTime”,
"@value" : “1996-10-22T08:02:00Z”
},
“j.0:days_supply-52” : “30”,
“j.0:drug-52” : “PROMETHAZINE 25MG TAB (PHENERGAN EQ)”,
“drug-52” : “http://chcs.taps.com/50-3704”,
“j.0:expanded_sig-52” : “TAKE ONE TABLET BY MOUTH~EVERY 6 TO 8 HOURS AS~NEEDED FOR NAUSEA~”,
“j.0:expiration_date-52” : {
"@type" : “xsd:dateTime”,
"@value" : “1996-11-21T00:00:00Z”
}]
}


#4

Hi Neda,

Have you tried N1QL DP3 and the syntax for ANY … SATISFIES?

-Gerald