How to iterate two object of array on condition ,outer array inside it inner array on condition

rkumar · July 29, 2016, 10:47am

Hi Guys ,my using CB server 4.5 and i’m facing a query (In N1QL) problem to fetch json data.
Below is my one document

select * from default limit 1;

[
{
“default”: {
“ACTIVE_FLAG”: true,
“ANONYMOUS_FLAG”: false,
“AUTHOR”: {},
“AUTHOR_POST_FLAG”: false,
“CONTENTS”: [
{
“ACTIVE_FLAG”: true,
“CREATED_BY”: "drschwartzman@drschwartzman.com",
“CREATED_TIME”: “2014-04-24 01:38:58”,
“ID”: 5006966,
“MD5”: “93cba07454f06a4a960172bbd6e2a435”,
“MODELS”: “",
“MODIFIED_BY”: "drschwartzman@drschwartzman.com",
“MODIFIED_TIME”: “2014-09-01 02:06:54”,
“NLP_FLAG”: false,
“OPTION_ID”: 1,
“PHI_FLAG”: false,
“PREDEFINED”: true,
“PROFANITY_FLAG”: false,
“PUBLISHING_API_STATUS”: “H”,
“QUESTION_ID”: 1299,
“RESPONSE”: “Yes”,
“RESPONSE_EN”: “Yes”,
“SENTIMENTS”: [
{
“ACTIVE_FLAG”: true,
“ANALYSIS_TIME”: “2014-04-24 01:38:58”,
“CATEGORY_ID”: 4026,
“CONTENT”: “Did you notice all the infection control barriers placed for your protection? Yes”,
“CONTENT_ID”: 5006966,
“CREATED_BY”: "admin@bfountain.com",
“CREATED_TIME”: “2014-09-01 08:36:54”,
“EXPERIENCE_TIME”: “2014-04-24 01:38:58”,
“FEATURE”: “barriers”,
“GROUP_ID”: “admin@bfountain.com_1409560614641”,
“ID”: 3398096,
“MODIFIED_BY”: "admin@bfountain.com",
“MODIFIED_TIME”: “2014-09-01 02:06:54”,
“NLP_QUALIFIER”: “MMM”,
“OPINION”: “Yes”,
“SCORE”: 1,
“WEIGHT”: 1
}
],
“STATUS”: “P”,
“WORD_COUNT”: 0
},
{
“ACTIVE_FLAG”: true,
“CREATED_BY”: "drschwartzman@drschwartzman.com",
“CREATED_TIME”: “2014-04-24 01:38:58”,
“ID”: 5006968,
“MD5”: “51f886667edd458c9a5357b06ac26a7f”,
“MODELS”: "”,
“MODIFIED_BY”: "drschwartzman@drschwartzman.com",
“MODIFIED_TIME”: “2014-09-01 02:06:54”,
“NLP_FLAG”: false,
“OPTION_ID”: 1,
“PHI_FLAG”: false,
“PREDEFINED”: true,
“PROFANITY_FLAG”: false,
“PUBLISHING_API_STATUS”: “H”,
“QUESTION_ID”: 1312,
“RESPONSE”: “Extremely”,
“RESPONSE_EN”: “Extremely”,
“SENTIMENTS”: [
{
“ACTIVE_FLAG”: true,
“ANALYSIS_TIME”: “2014-04-24 01:38:58”,
“CATEGORY_ID”: 4039,
“CONTENT”: “Would you feel comfortable referring your friends and family? Extremely”,
“CONTENT_ID”: 5006968,
“CREATED_BY”: "admin@bfountain.com",
“CREATED_TIME”: “2014-09-01 08:36:54”,
“EXPERIENCE_TIME”: “2014-04-24 01:38:58”,
“FEATURE”: “referring”,
“GROUP_ID”: “admin@bfountain.com_1409560614648”,
“ID”: 3398097,
“MODIFIED_BY”: "admin@bfountain.com",
“MODIFIED_TIME”: “2014-09-01 02:06:54”,
“NLP_QUALIFIER”: “MMM”,
“OPINION”: “Extremely”,
“SCORE”: 1,
“WEIGHT”: 1
}
],
“STATUS”: “P”,
“WORD_COUNT”: 0
},
{
“ACTIVE_FLAG”: true,
“CREATED_BY”: "drschwartzman@drschwartzman.com",
“CREATED_TIME”: “2014-04-24 01:38:58”,
“ID”: 5006969,
“MD5”: “93cba07454f06a4a960172bbd6e2a435”,
“MODELS”: “",
“MODIFIED_BY”: "drschwartzman@drschwartzman.com",
“MODIFIED_TIME”: “2014-09-01 02:06:54”,
“NLP_FLAG”: false,
“OPTION_ID”: 1,
“PHI_FLAG”: false,
“PREDEFINED”: true,
“PROFANITY_FLAG”: false,
“PUBLISHING_API_STATUS”: “H”,
“QUESTION_ID”: 1289,
“RESPONSE”: “Yes”,
“RESPONSE_EN”: “Yes”,
“SENTIMENTS”: [
{
“ACTIVE_FLAG”: true,
“ANALYSIS_TIME”: “2014-04-24 01:38:58”,
“CATEGORY_ID”: 4014,
“CONTENT”: “Were you seen at your appointed time? Yes”,
“CONTENT_ID”: 5006969,
“CREATED_BY”: "admin@bfountain.com",
“CREATED_TIME”: “2014-09-01 08:36:54”,
“EXPERIENCE_TIME”: “2014-04-24 01:38:58”,
“FEATURE”: “seen”,
“GROUP_ID”: “admin@bfountain.com_1409560614655”,
“ID”: 3398098,
“MODIFIED_BY”: "admin@bfountain.com",
“MODIFIED_TIME”: “2014-09-01 02:06:54”,
“NLP_QUALIFIER”: “MMM”,
“OPINION”: “Yes”,
“SCORE”: 1,
“WEIGHT”: 1
}
],
“STATUS”: “P”,
“WORD_COUNT”: 0
},
{
“ACTIVE_FLAG”: true,
“CREATED_BY”: "drschwartzman@drschwartzman.com",
“CREATED_TIME”: “2014-04-24 01:38:58”,
“ID”: 5006970,
“MD5”: “93cba07454f06a4a960172bbd6e2a435”,
“MODELS”: "”,
“MODIFIED_BY”: "drschwartzman@drschwartzman.com",
“MODIFIED_TIME”: “2014-09-01 02:06:54”,
“NLP_FLAG”: false,
“OPTION_ID”: 1,
“PHI_FLAG”: false,
“PREDEFINED”: true,
“PROFANITY_FLAG”: false,
“PUBLISHING_API_STATUS”: “H”,
“QUESTION_ID”: 1302,
“RESPONSE”: “Yes”,
“RESPONSE_EN”: “Yes”,
“SENTIMENTS”: [
{
“ACTIVE_FLAG”: true,
“ANALYSIS_TIME”: “2014-04-24 01:38:58”,
“CATEGORY_ID”: 4032,
“CONTENT”: “Were you personally escorted for checkout? Yes”,
“CONTENT_ID”: 5006970,
“CREATED_BY”: "admin@bfountain.com",
“CREATED_TIME”: “2014-09-01 08:36:54”,
“EXPERIENCE_TIME”: “2014-04-24 01:38:58”,
“FEATURE”: “escorted”,
“GROUP_ID”: “admin@bfountain.com_1409560614662”,
“ID”: 3398099,
“MODIFIED_BY”: "admin@bfountain.com",
“MODIFIED_TIME”: “2014-09-01 02:06:54”,
“NLP_QUALIFIER”: “MMM”,
“OPINION”: “Yes”,
“SCORE”: 1,
“WEIGHT”: 1
}
],
“STATUS”: “P”,
“WORD_COUNT”: 0
},
{
“ACTIVE_FLAG”: true,
“CREATED_BY”: "drschwartzman@drschwartzman.com",
“CREATED_TIME”: “2014-04-24 01:38:58”,
“ID”: 5006971,
“MD5”: “b5f0eedb0c0a55173ea689d50c5fcb78”,
“MODELS”: “",
“MODIFIED_BY”: "drschwartzman@drschwartzman.com",
“MODIFIED_TIME”: “2014-09-01 02:06:54”,
“NLP_FLAG”: false,
“OPTION_ID”: 2,
“PHI_FLAG”: false,
“PREDEFINED”: true,
“PROFANITY_FLAG”: false,
“PUBLISHING_API_STATUS”: “H”,
“QUESTION_ID”: 1305,
“RESPONSE”: “Mostly”,
“RESPONSE_EN”: “Mostly”,
“SENTIMENTS”: [
{
“ACTIVE_FLAG”: true,
“ANALYSIS_TIME”: “2014-04-24 01:38:58”,
“CATEGORY_ID”: 4016,
“CONTENT”: “Were the anticipated costs for your next appointment explained? Mostly”,
“CONTENT_ID”: 5006971,
“CREATED_BY”: "admin@bfountain.com",
“CREATED_TIME”: “2014-09-01 08:36:54”,
“EXPERIENCE_TIME”: “2014-04-24 01:38:58”,
“FEATURE”: “explained”,
“GROUP_ID”: “admin@bfountain.com_1409560614669”,
“ID”: 3398100,
“MODIFIED_BY”: "admin@bfountain.com",
“MODIFIED_TIME”: “2014-09-01 02:06:54”,
“NLP_QUALIFIER”: “MMM”,
“OPINION”: “Mostly”,
“SCORE”: 0.5,
“WEIGHT”: 1
}
],
“STATUS”: “P”,
“WORD_COUNT”: 0
},
{
“ACTIVE_FLAG”: true,
“CREATED_BY”: "drschwartzman@drschwartzman.com",
“CREATED_TIME”: “2014-04-24 01:38:58”,
“ID”: 5006972,
“MD5”: “93cba07454f06a4a960172bbd6e2a435”,
“MODELS”: "”,
“MODIFIED_BY”: "drschwartzman@drschwartzman.com",
“MODIFIED_TIME”: “2014-09-01 02:06:54”,
“NLP_FLAG”: false,
“OPTION_ID”: 1,
“PHI_FLAG”: false,
“PREDEFINED”: true,
“PROFANITY_FLAG”: false,
“PUBLISHING_API_STATUS”: “H”,
“QUESTION_ID”: 1282,
“RESPONSE”: “Yes”,
“RESPONSE_EN”: “Yes”,
“SENTIMENTS”: [
{
“ACTIVE_FLAG”: true,
“ANALYSIS_TIME”: “2014-04-24 01:38:58”,
“CATEGORY_ID”: 4025,
“CONTENT”: “If you received a recorded message, was your call returned in a timely manner? Yes”,
“CONTENT_ID”: 5006972,
“CREATED_BY”: "admin@bfountain.com",
“CREATED_TIME”: “2014-09-01 08:36:54”,
“EXPERIENCE_TIME”: “2014-04-24 01:38:58”,
“FEATURE”: “call returned”,
“GROUP_ID”: “admin@bfountain.com_1409560614677”,
“ID”: 3398101,
“MODIFIED_BY”: "admin@bfountain.com",
“MODIFIED_TIME”: “2014-09-01 02:06:54”,
“NLP_QUALIFIER”: “MMM”,
“OPINION”: “Yes”,
“SCORE”: 1,
“WEIGHT”: 1
}
],
“STATUS”: “P”,
“WORD_COUNT”: 0
},
{
“ACTIVE_FLAG”: true,
“CREATED_BY”: "drschwartzman@drschwartzman.com",
“CREATED_TIME”: “2014-04-24 01:38:58”,
“ID”: 5006973,
“MD5”: “0b57243c04223bc925c46a540247a8d6”,
“MODELS”: “",
“MODIFIED_BY”: "drschwartzman@drschwartzman.com",
“MODIFIED_TIME”: “2014-09-01 02:06:54”,
“NLP_FLAG”: false,
“OPTION_ID”: 1,
“PHI_FLAG”: false,
“PREDEFINED”: true,
“PROFANITY_FLAG”: false,
“PUBLISHING_API_STATUS”: “H”,
“QUESTION_ID”: 1295,
“RESPONSE”: “Completely”,
“RESPONSE_EN”: “Completely”,
“SENTIMENTS”: [
{
“ACTIVE_FLAG”: true,
“ANALYSIS_TIME”: “2014-04-24 01:38:58”,
“CATEGORY_ID”: 4016,
“CONTENT”: “Were future treatment needs explained? Completely”,
“CONTENT_ID”: 5006973,
“CREATED_BY”: "admin@bfountain.com",
“CREATED_TIME”: “2014-09-01 08:36:54”,
“EXPERIENCE_TIME”: “2014-04-24 01:38:58”,
“FEATURE”: “explained”,
“GROUP_ID”: “admin@bfountain.com_1409560614684”,
“ID”: 3398102,
“MODIFIED_BY”: "admin@bfountain.com",
“MODIFIED_TIME”: “2014-09-01 02:06:54”,
“NLP_QUALIFIER”: “MMM”,
“OPINION”: “Completely”,
“SCORE”: 1,
“WEIGHT”: 1
}
],
“STATUS”: “P”,
“WORD_COUNT”: 0
},
{
“ACTIVE_FLAG”: true,
“CREATED_BY”: "drschwartzman@drschwartzman.com",
“CREATED_TIME”: “2014-04-24 01:38:58”,
“ID”: 5006975,
“MD5”: “93cba07454f06a4a960172bbd6e2a435”,
“MODELS”: "”,
“MODIFIED_BY”: "drschwartzman@drschwartzman.com",
“MODIFIED_TIME”: “2014-09-01 02:06:54”,
“NLP_FLAG”: false,
“OPTION_ID”: 1,
“PHI_FLAG”: false,
“PREDEFINED”: true,
“PROFANITY_FLAG”: false,
“PUBLISHING_API_STATUS”: “H”,
“QUESTION_ID”: 1285,
“RESPONSE”: “Yes”,
“RESPONSE_EN”: “Yes”,
“SENTIMENTS”: [
{
“ACTIVE_FLAG”: true,
“ANALYSIS_TIME”: “2014-04-24 01:38:58”,
“CATEGORY_ID”: 4034,
“CONTENT”: “Were you greeted when you entered? Yes”,
“CONTENT_ID”: 5006975,
“CREATED_BY”: "admin@bfountain.com",
“CREATED_TIME”: “2014-09-01 08:36:54”,
“EXPERIENCE_TIME”: “2014-04-24 01:38:58”,
“FEATURE”: “greeted”,
“GROUP_ID”: “admin@bfountain.com_1409560614691”,
“ID”: 3398103,
“MODIFIED_BY”: "admin@bfountain.com",
“MODIFIED_TIME”: “2014-09-01 02:06:54”,
“NLP_QUALIFIER”: “MMM”,
“OPINION”: “Yes”,
“SCORE”: 1,
“WEIGHT”: 1
}
],
“STATUS”: “P”,
“WORD_COUNT”: 0
},
{
“ACTIVE_FLAG”: true,
“CREATED_BY”: "drschwartzman@drschwartzman.com",
“CREATED_TIME”: “2014-04-24 01:38:58”,
“ID”: 5006976,
“MD5”: “c233708be06b70e96b6350cff6f0b523”,
“MODELS”: “",
“MODIFIED_BY”: "drschwartzman@drschwartzman.com",
“MODIFIED_TIME”: “2014-09-01 02:06:54”,
“NLP_FLAG”: false,
“OPTION_ID”: 2,
“PHI_FLAG”: false,
“PREDEFINED”: true,
“PROFANITY_FLAG”: false,
“PUBLISHING_API_STATUS”: “H”,
“QUESTION_ID”: 1311,
“RESPONSE”: "Moderately Aware ",
“RESPONSE_EN”: "Moderately Aware “,
“STATUS”: “P”,
“WORD_COUNT”: 0
},
{
“ACTIVE_FLAG”: true,
“CREATED_BY”: "drschwartzman@drschwartzman.com”,
“CREATED_TIME”: “2014-04-24 01:38:58”,
“ID”: 5006977,
“MD5”: “bafd7322c6e97d25b6299b5d6fe8920b”,
“MODELS”: "”,
“MODIFIED_BY”: "drschwartzman@drschwartzman.com",
“MODIFIED_TIME”: “2014-09-01 02:06:54”,
“NLP_FLAG”: false,
“OPTION_ID”: 2,
“PHI_FLAG”: false,
“PREDEFINED”: true,
“PROFANITY_FLAG”: false,
“PUBLISHING_API_STATUS”: “H”,
“QUESTION_ID”: 1304,
“RESPONSE”: “No”,
“RESPONSE_EN”: “No”,
“SENTIMENTS”: [
{
“ACTIVE_FLAG”: true,
“ANALYSIS_TIME”: “2014-04-24 01:38:58”,
“CATEGORY_ID”: 4016,
“CONTENT”: “Were you presented with a treatment plan? No”,
“CONTENT_ID”: 5006977,
“CREATED_BY”: "admin@bfountain.com",
“CREATED_TIME”: “2014-09-01 08:36:54”,
“EXPERIENCE_TIME”: “2014-04-24 01:38:58”,
“FEATURE”: “treatment plan”,
“GROUP_ID”: “admin@bfountain.com_1409560614700”,
“ID”: 3398104,
“MODIFIED_BY”: "admin@bfountain.com",
“MODIFIED_TIME”: “2014-09-01 02:06:54”,
“NLP_QUALIFIER”: “MMM”,
“OPINION”: “No”,
“SCORE”: -1,
“WEIGHT”: 1
}
],
“STATUS”: “P”,
“WORD_COUNT”: 0
},
{
“ACTIVE_FLAG”: true,
“CREATED_BY”: "drschwartzman@drschwartzman.com",
“CREATED_TIME”: “2014-04-24 01:38:58”,
“ID”: 5006978,
“MD5”: “0b57243c04223bc925c46a540247a8d6”,
“MODELS”: “",
“MODIFIED_BY”: "drschwartzman@drschwartzman.com",
“MODIFIED_TIME”: “2014-09-01 02:06:54”,
“NLP_FLAG”: false,
“OPTION_ID”: 1,
“PHI_FLAG”: false,
“PREDEFINED”: true,
“PROFANITY_FLAG”: false,
“PUBLISHING_API_STATUS”: “H”,
“QUESTION_ID”: 1294,
“RESPONSE”: “Completely”,
“RESPONSE_EN”: “Completely”,
“SENTIMENTS”: [
{
“ACTIVE_FLAG”: true,
“ANALYSIS_TIME”: “2014-04-24 01:38:58”,
“CATEGORY_ID”: 4017,
“CONTENT”: “During today’s visit, did you feel that all your needs/questions were addressed appropriately? Completely”,
“CONTENT_ID”: 5006978,
“CREATED_BY”: "admin@bfountain.com",
“CREATED_TIME”: “2014-09-01 08:36:54”,
“EXPERIENCE_TIME”: “2014-04-24 01:38:58”,
“FEATURE”: “addressed”,
“GROUP_ID”: “admin@bfountain.com_1409560614708”,
“ID”: 3398105,
“MODIFIED_BY”: "admin@bfountain.com",
“MODIFIED_TIME”: “2014-09-01 02:06:54”,
“NLP_QUALIFIER”: “MMM”,
“OPINION”: “Completely”,
“SCORE”: 1,
“WEIGHT”: 1
}
],
“STATUS”: “P”,
“WORD_COUNT”: 0
},
{
“ACTIVE_FLAG”: true,
“CREATED_BY”: "drschwartzman@drschwartzman.com",
“CREATED_TIME”: “2014-04-24 01:38:58”,
“ID”: 5006979,
“MD5”: “51f886667edd458c9a5357b06ac26a7f”,
“MODELS”: "”,
“MODIFIED_BY”: "drschwartzman@drschwartzman.com",
“MODIFIED_TIME”: “2014-09-01 02:06:54”,
“NLP_FLAG”: false,
“OPTION_ID”: 1,
“PHI_FLAG”: false,
“PREDEFINED”: true,
“PROFANITY_FLAG”: false,
“PUBLISHING_API_STATUS”: “H”,
“QUESTION_ID”: 1297,
“RESPONSE”: “Extremely”,
“RESPONSE_EN”: “Extremely”,
“SENTIMENTS”: [
{
“ACTIVE_FLAG”: true,
“ANALYSIS_TIME”: “2014-04-24 01:38:58”,
“CATEGORY_ID”: 4026,
“CONTENT”: “Was the treatment room comfortable and spotlessly clean?? Extremely”,
“CONTENT_ID”: 5006979,
“CREATED_BY”: "admin@bfountain.com",
“CREATED_TIME”: “2014-09-01 08:36:54”,
“EXPERIENCE_TIME”: “2014-04-24 01:38:58”,
“FEATURE”: “treatment room”,
“GROUP_ID”: “admin@bfountain.com_1409560614715”,
“ID”: 3398106,
“MODIFIED_BY”: "admin@bfountain.com",
“MODIFIED_TIME”: “2014-09-01 02:06:54”,
“NLP_QUALIFIER”: “MMM”,
“OPINION”: “Extremely”,
“SCORE”: 1,
“WEIGHT”: 1
}
],
“STATUS”: “P”,
“WORD_COUNT”: 0
},
{
“ACTIVE_FLAG”: true,
“CREATED_BY”: "drschwartzman@drschwartzman.com",
“CREATED_TIME”: “2014-04-24 01:38:58”,
“ID”: 5006981,
“MD5”: “fcc2d28a33d2df558f18766e067569c6”,
“MODELS”: “",
“MODIFIED_BY”: "drschwartzman@drschwartzman.com",
“MODIFIED_TIME”: “2014-09-01 02:06:54”,
“NLP_FLAG”: false,
“OPTION_ID”: 1,
“PHI_FLAG”: false,
“PREDEFINED”: true,
“PROFANITY_FLAG”: false,
“PUBLISHING_API_STATUS”: “H”,
“QUESTION_ID”: 1277,
“RESPONSE”: “Excellent”,
“RESPONSE_EN”: “Excellent”,
“SENTIMENTS”: [
{
“ACTIVE_FLAG”: true,
“ANALYSIS_TIME”: “2014-04-24 01:38:58”,
“CATEGORY_ID”: 4011,
“CONTENT”: “How would you rate the appointment availability? Excellent”,
“CONTENT_ID”: 5006981,
“CREATED_BY”: "admin@bfountain.com",
“CREATED_TIME”: “2014-09-01 08:36:54”,
“EXPERIENCE_TIME”: “2014-04-24 01:38:58”,
“FEATURE”: “appointment”,
“GROUP_ID”: “admin@bfountain.com_1409560614721”,
“ID”: 3398107,
“MODIFIED_BY”: "admin@bfountain.com",
“MODIFIED_TIME”: “2014-09-01 02:06:54”,
“NLP_QUALIFIER”: “MMM”,
“OPINION”: “Excellent”,
“SCORE”: 1,
“WEIGHT”: 1
}
],
“STATUS”: “P”,
“WORD_COUNT”: 0
},
{
“ACTIVE_FLAG”: true,
“CREATED_BY”: "drschwartzman@drschwartzman.com",
“CREATED_TIME”: “2014-04-24 01:38:58”,
“ID”: 5006982,
“MD5”: “51f886667edd458c9a5357b06ac26a7f”,
“MODELS”: "”,
“MODIFIED_BY”: "drschwartzman@drschwartzman.com",
“MODIFIED_TIME”: “2014-09-01 02:06:54”,
“NLP_FLAG”: false,
“OPTION_ID”: 1,
“PHI_FLAG”: false,
“PREDEFINED”: true,
“PROFANITY_FLAG”: false,
“PUBLISHING_API_STATUS”: “H”,
“QUESTION_ID”: 1303,
“RESPONSE”: “Extremely”,
“RESPONSE_EN”: “Extremely”,
“SENTIMENTS”: [
{
“ACTIVE_FLAG”: true,
“ANALYSIS_TIME”: “2014-04-24 01:38:58”,
“CATEGORY_ID”: 4033,
“CONTENT”: “Did you feel that the person checking you out was properly aware of your treatment today and what your next appointment would be? Extremely”,
“CONTENT_ID”: 5006982,
“CREATED_BY”: "admin@bfountain.com",
“CREATED_TIME”: “2014-09-01 08:36:54”,
“EXPERIENCE_TIME”: “2014-04-24 01:38:58”,
“FEATURE”: “checking you out”,
“GROUP_ID”: “admin@bfountain.com_1409560614727”,
“ID”: 3398108,
“MODIFIED_BY”: "admin@bfountain.com",
“MODIFIED_TIME”: “2014-09-01 02:06:54”,
“NLP_QUALIFIER”: “MMM”,
“OPINION”: “Extremely”,
“SCORE”: 1,
“WEIGHT”: 1
}
],
“STATUS”: “P”,
“WORD_COUNT”: 0
},
{
“ACTIVE_FLAG”: true,
“CREATED_BY”: "drschwartzman@drschwartzman.com",
“CREATED_TIME”: “2014-04-24 01:38:58”,
“ID”: 5006983,
“MD5”: “51f886667edd458c9a5357b06ac26a7f”,
“MODELS”: “",
“MODIFIED_BY”: "drschwartzman@drschwartzman.com",
“MODIFIED_TIME”: “2014-09-01 02:06:54”,
“NLP_FLAG”: false,
“OPTION_ID”: 1,
“PHI_FLAG”: false,
“PREDEFINED”: true,
“PROFANITY_FLAG”: false,
“PUBLISHING_API_STATUS”: “H”,
“QUESTION_ID”: 1283,
“RESPONSE”: “Extremely”,
“RESPONSE_EN”: “Extremely”,
“SENTIMENTS”: [
{
“ACTIVE_FLAG”: true,
“ANALYSIS_TIME”: “2014-04-24 01:38:58”,
“CATEGORY_ID”: 4012,
“CONTENT”: “Was parking adequate? Extremely”,
“CONTENT_ID”: 5006983,
“CREATED_BY”: "admin@bfountain.com",
“CREATED_TIME”: “2014-09-01 08:36:54”,
“EXPERIENCE_TIME”: “2014-04-24 01:38:58”,
“FEATURE”: “parking”,
“GROUP_ID”: “admin@bfountain.com_1409560614734”,
“ID”: 3398109,
“MODIFIED_BY”: "admin@bfountain.com",
“MODIFIED_TIME”: “2014-09-01 02:06:54”,
“NLP_QUALIFIER”: “MMM”,
“OPINION”: “Extremely”,
“SCORE”: 1,
“WEIGHT”: 1
}
],
“STATUS”: “P”,
“WORD_COUNT”: 0
},
{
“ACTIVE_FLAG”: true,
“CREATED_BY”: "drschwartzman@drschwartzman.com",
“CREATED_TIME”: “2014-04-24 01:38:58”,
“ID”: 5006985,
“MD5”: “51f886667edd458c9a5357b06ac26a7f”,
“MODELS”: "”,
“MODIFIED_BY”: "drschwartzman@drschwartzman.com",
“MODIFIED_TIME”: “2014-09-01 02:06:54”,
“NLP_FLAG”: false,
“OPTION_ID”: 1,
“PHI_FLAG”: false,
“PREDEFINED”: true,
“PROFANITY_FLAG”: false,
“PUBLISHING_API_STATUS”: “H”,
“QUESTION_ID”: 1286,
“RESPONSE”: “Extremely”,
“RESPONSE_EN”: “Extremely”,
“SENTIMENTS”: [
{
“ACTIVE_FLAG”: true,
“ANALYSIS_TIME”: “2014-04-24 01:38:58”,
“CATEGORY_ID”: 4030,
“CONTENT”: “Did you find our reception area comfortable? Extremely”,
“CONTENT_ID”: 5006985,
“CREATED_BY”: "admin@bfountain.com",
“CREATED_TIME”: “2014-09-01 08:36:54”,
“EXPERIENCE_TIME”: “2014-04-24 01:38:58”,
“FEATURE”: “comfortable”,
“GROUP_ID”: “admin@bfountain.com_1409560614749”,
“ID”: 3398112,
“MODIFIED_BY”: "admin@bfountain.com",
“MODIFIED_TIME”: “2014-09-01 02:06:54”,
“NLP_QUALIFIER”: “MMM”,
“OPINION”: “Extremely”,
“SCORE”: 1,
“WEIGHT”: 1
}
],
“STATUS”: “P”,
“WORD_COUNT”: 0
},
{
“ACTIVE_FLAG”: true,
“CREATED_BY”: "drschwartzman@drschwartzman.com",
“CREATED_TIME”: “2014-04-24 01:38:58”,
“ID”: 5006974,
“MD5”: “9f7aa8ce719604f269027cacec634cf1”,
“MODELS”: “",
“MODIFIED_BY”: "drschwartzman@drschwartzman.com",
“MODIFIED_TIME”: “2014-09-03 06:38:27”,
“NLP_FLAG”: false,
“OPTION_ID”: 1,
“PHI_FLAG”: false,
“PREDEFINED”: false,
“PROFANITY_FLAG”: false,
“PUBLISHING_API_STATUS”: “H”,
“QUESTION_ID”: 1308,
“RESPONSE”: “6 months”,
“RESPONSE_EN”: “6 months”,
“STATUS”: “T”,
“WORD_COUNT”: 0
},
{
“ACTIVE_FLAG”: true,
“CREATED_BY”: "drschwartzman@drschwartzman.com",
“CREATED_TIME”: “2014-04-24 01:38:58”,
“ID”: 5006980,
“MD5”: “77d3467e281b93f128a0192e84056923”,
“MODELS”: "”,
“MODIFIED_BY”: "drschwartzman@drschwartzman.com",
“MODIFIED_TIME”: “2014-09-03 06:38:27”,
“NLP_FLAG”: false,
“OPTION_ID”: 11491,
“PHI_FLAG”: false,
“PREDEFINED”: true,
“PROFANITY_FLAG”: false,
“PUBLISHING_API_STATUS”: “H”,
“QUESTION_ID”: 1945,
“RESPONSE”: “Fabiana Steren Offit, DDS”,
“RESPONSE_EN”: “Fabiana Steren Offit, DDS”,
“STATUS”: “T”,
“WORD_COUNT”: 0
},
{
“ACTIVE_FLAG”: true,
“CREATED_BY”: "drschwartzman@drschwartzman.com",
“CREATED_TIME”: “2014-04-24 01:38:58”,
“ID”: 5006984,
“MD5”: “0ea4e2ba0e2592c4643d07a2ce3d0992”,
“MODELS”: “*”,
“MODIFIED_BY”: "jthomas@bfountain.com",
“MODIFIED_TIME”: “2014-10-01 02:21:49”,
“NLP_FLAG”: false,
“OPTION_ID”: 1,
“PHI_FLAG”: false,
“PREDEFINED”: true,
“PROFANITY_FLAG”: false,
“PUBLISHING_API_STATUS”: “H”,
“QUESTION_ID”: 1280,
“RESPONSE”: “Completely satisfied “,
“RESPONSE_EN”: “Completely satisfied “,
“STATUS”: “Q”,
“WORD_COUNT”: 0
},
{
“ACTIVE_FLAG”: true,
“CREATED_BY”: "drschwartzman@drschwartzman.com”,
“CREATED_TIME”: “2014-04-24 01:38:58”,
“ID”: 5006967,
“MD5”: “2d416995e472ff5e2a83422a621ace26”,
“MODELS”: “phi”,
“MODIFIED_BY”: "thread_nlp3@bfountain.com”,
“MODIFIED_TIME”: “2015-08-18 05:36:07”,
“NLP_FLAG”: true,
“OPTION_ID”: 1,
“PHI_FLAG”: false,
“PREDEFINED”: false,
“PROFANITY_FLAG”: false,
“PUBLISHING_API_STATUS”: “Y”,
“QUESTION_ID”: 1309,
“RESPONSE”: “great service”,
“RESPONSE_EN”: “great service”,
“SENTIMENTS”: [
{
“ACTIVE_FLAG”: true,
“ANALYSIS_TIME”: “2014-04-26 12:00:00”,
“CATEGORY_ID”: 4000,
“CONTENT”: “great service\r\n. “,
“CONTENT_ID”: 5006967,
“CREATED_BY”: "npaaru@bfountain.com”,
“CREATED_TIME”: “2014-04-26 07:20:57”,
“EXPERIENCE_TIME”: “2014-04-24 12:00:00”,
“FEATURE”: “service”,
“GROUP_ID”: “npaaru@bfountain.com_1398494917464”,
“ID”: 1272326,
“MODIFIED_BY”: "npaaru@bfountain.com”,
“MODIFIED_TIME”: “2014-08-30 02:30:13”,
“NLP_QUALIFIER”: “MMM”,
“OPINION”: “great”,
“SCORE”: 1,
“WEIGHT”: 1
}
],
“STATUS”: “P”,
“WORD_COUNT”: 2
}
],
“CREATED_BY”: "drschwartzman@drschwartzman.com”,
“CREATED_TIME”: “2014-04-24 07:08:58”,
“DEFAULT_LANG”: “en”,
“ENGAGEMENTS”: [],
“HARVESTER_ID”: 0,
“HAS_TESTIMONIAL”: true,
“ID”: 1732611,
“LOCATION_ID”: 11520,
“MENTION_TIME”: “2014-04-24 07:08:58”,
“MODIFIED_BY”: "drschwartzman@drschwartzman.com”,
“MODIFIED_TIME”: “2014-08-30 07:19:12”,
“OWNER_POST_FLAG”: false,
“PERSON_ID”: 11491,
“PRODUCT_ID”: “patient”,
“PUBLISH_FLAG”: true,
“RECOMMEND_FLAG”: true,
“REVIEW_COUNT”: 1,
“SOURCE_ID”: 277,
“SURVEY_MODE”: “tablet”,
“SURVEY_TYPE”: “SURVEY”
}
}
]

Q1-Now the problem i’m facing is that ,i want to iterate sentiments and sentiments data on basis of content
like where contents.id=‘12’.
Q2- how only get sentiments array
Q3- how to point array inside array,lke i want to point inner array value,e.g: i want to make condition of sentiments any value

geraldss · July 29, 2016, 6:30pm

You need to look at the collection operators in N1QL. I would suggest you spend some time on the documentation and playing around with queries using your data.

Look at the following operators:

ANY
EVERY
ARRAY
FIRST

These operators can be nested, so you can access your nested arrays.

rkumar · August 2, 2016, 6:56am

Hi Thank you for this reply.
i;m working on this,but its a gentel request ,can you provide me some query which iterates sentiments based on
CATEGORY_ID in SENTIMENTS object array.

geraldss · August 2, 2016, 2:18pm

Q1-Now the problem i’m facing is that ,i want to iterate sentiments and sentiments data on basis of content
like where contents.id=‘12’.

ANY / EVERY s IN SENTIMENTS SATISFIES s.CONTENT_ID = ‘12’ END
ARRAY / FIRST s FOR s IN SENTIMENTS WHEN s.CONTENT_ID = ‘12’ END

Q2- how only get sentiments array

SENTIMENTS

Q3- how to point array inside array,lke i want to point inner array value,e.g: i want to make condition of sentiments any value

rkumar · August 5, 2016, 6:05am

i’m facing problem to make a query which satisfied my need as below.

i have a document D1,which have more than one contents object and each contents have nos of sentiments(Refer pasted docs).in sentiments we have score as well as category_id,so i need average of score.but i m fail to give the range what we did in sql using ‘IN’ keyword like below

select avg(score) from D1 with category_id in (4000,4024);

thank you in advance.

geraldss · August 5, 2016, 6:36pm

In N1QL, you use square brackets for IN.

IN [ value1, value2, … ]

rkumar · August 8, 2016, 5:03am

Hi Geraldss,

really thankfull for your reply. still need your help in below documents and query.
so below is the json structure.

{
“AUTHOR”:{

},
“SURVEY_TYPE”:“FEEDBACK”,
“AUTHOR_POST_FLAG”:false,
“PRODUCT_ID”:“patient”,
“DEFAULT_LANG”:“en”,
“MENTION_TIME”:“2008-06-07 05:30:00”,
“SURVEY_MODE”:“HARVESTER”,
“HAS_TESTIMONIAL”:true,
“ENGAGEMENTS”:[

],
“MODIFIED_TIME”:“2014-03-11 05:02:19”,
“CREATED_BY”:"admin@bfountain.com",
“ID”:21206,
“MODIFIED_BY”:"npaaru@bfountain.com",
“URL”:“http://local.yahoo.com/info-12499689-schwartzman-paul-d-dds-rockville#reviews”,
“ANONYMOUS_FLAG”:false,
“PUBLISH_FLAG”:false,
“ACTIVE_FLAG”:true,
“SOURCE_ID”:100115,
“CONTENTS”:[
{
“ACTIVE_FLAG”:true,
“RESPONSE_EN”:“http://local.yahoo.com/info-12499689-schwartzman-paul-d-dds-rockville#reviews”,
“PROFANITY_FLAG”:false,
“PREDEFINED”:false,
“MD5”:“1c74caa97544ab0b45386108728e834c”,
“QUESTION_ID”:1000000001,
“STATUS”:“P”,
“PUBLISHING_API_STATUS”:“H”,
“OPTION_ID”:1,
“MODIFIED_TIME”:“2014-03-11 05:02:19”,
“CREATED_BY”:"admin@bfountain.com",
“PHI_FLAG”:false,
“RESPONSE”:“http://local.yahoo.com/info-12499689-schwartzman-paul-d-dds-rockville#reviews”,
“ID”:3218772,
“MODELS”:"*",
“MODIFIED_BY”:"npaaru@bfountain.com",
“WORD_COUNT”:0,
“NLP_FLAG”:false,
“CREATED_TIME”:“2014-03-11 09:18:29”
},
{
“ACTIVE_FLAG”:true,
“RESPONSE_EN”:“Dr. Schwartzman was my dentist for 5 years. He initially diagnosed me with periodontal disease due to gum pockets of 4-5mm. I underwent multiple deep scale cleanings and followed his recommendations for at home care, including electric toothbrush and rinse, besides of course daily flossing. \r\n \r\nHe and his staff are technically proficient from what I can judge. My issues with him are pricing and possibly excessive treatment. After 5 years of extensive treatment, he still found the gum pockets of 3-4mm which he wrote off as anatomical. It seems they were expecting to get 1-2mm max. \r\n \r\nWhile there’s nothing wrong with the diagnosis, they repeated treatments and kept me on a periodontal 4x / year cleaning schedule that insurance would not fully cover. Billings even for covered service were always in excess of insurance rates. \r\n \r\nWhile he and his staff are polite and capable, if you want to use him, be aware that you will pay a lot out of pocket for his service. You may want a second opinion to be sure there is not intentional overcharging for unnecessary service.”,
“PROFANITY_FLAG”:false,
“PREDEFINED”:false,
“MD5”:“911d10c91c6c80afb0a1a4d4ab786d24”,
“QUESTION_ID”:1000000000,
“STATUS”:“P”,
“PUBLISHING_API_STATUS”:“N”,
“OPTION_ID”:1,
“MODIFIED_TIME”:“2015-08-18 10:31:08”,
“SENTIMENTS”:[
{
“GROUP_ID”:“npaaru@bfountain.com_1394536948561”,
“CONTENT_ID”:2175967,
“ACTIVE_FLAG”:true,
“SCORE”:1,
“OPINION”:“technically”,
“NLP_QUALIFIER”:“MMM”,
“MODIFIED_TIME”:“2014-08-30 10:58:15”,
“CREATED_BY”:"npaaru@bfountain.com",
“CONTENT”:“I underwent multiple deep scale cleanings and followed his recommendations for at home care, including electric toothbrush and rinse, besides of course daily flossing. He and his staff are technically proficient from what I can judge. “,
“WEIGHT”:1,
“ID”:992554,
“EXPERIENCE_TIME”:“2008-06-07 12:00:00”,
“MODIFIED_BY”:"npaaru@bfountain.com”,
“CATEGORY_ID”:4015,
“ANALYSIS_TIME”:“2014-03-11 12:00:00”,
“FEATURE”:“proficient”,
“CREATED_TIME”:“2014-03-11 11:24:07”
},
{
“GROUP_ID”:“npaaru@bfountain.com_1394537047233”,
“CONTENT_ID”:2175967,
“ACTIVE_FLAG”:true,
“SCORE”:1,
“OPINION”:“proficient”,
“NLP_QUALIFIER”:“MMM”,
“MODIFIED_TIME”:“2014-08-30 10:58:15”,
“CREATED_BY”:"npaaru@bfountain.com”,
“CONTENT”:“I underwent multiple deep scale cleanings and followed his recommendations for at home care, including electric toothbrush and rinse, besides of course daily flossing. He and his staff are technically proficient from what I can judge. “,
“WEIGHT”:1,
“ID”:992555,
“EXPERIENCE_TIME”:“2008-06-07 12:00:00”,
“MODIFIED_BY”:"npaaru@bfountain.com”,
“CATEGORY_ID”:4021,
“ANALYSIS_TIME”:“2014-03-11 12:00:00”,
“FEATURE”:“staff”,
“CREATED_TIME”:“2014-03-11 11:25:38”
},
{
“GROUP_ID”:“npaaru@bfountain.com_1394537138347”,
“CONTENT_ID”:2175967,
“ACTIVE_FLAG”:true,
“SCORE”:-1,
“OPINION”:“issues”,
“NLP_QUALIFIER”:“MMM”,
“MODIFIED_TIME”:“2014-08-30 10:58:15”,
“CREATED_BY”:"npaaru@bfountain.com”,
“CONTENT”:“My issues with him are pricing and possibly excessive treatment. “,
“WEIGHT”:1,
“ID”:992556,
“EXPERIENCE_TIME”:“2008-06-07 12:00:00”,
“MODIFIED_BY”:"npaaru@bfountain.com”,
“CATEGORY_ID”:4035,
“ANALYSIS_TIME”:“2014-03-11 12:00:00”,
“FEATURE”:“pricing”,
“CREATED_TIME”:“2014-03-11 11:27:27”
},
{
“GROUP_ID”:“npaaru@bfountain.com_1394537247428”,
“CONTENT_ID”:2175967,
“ACTIVE_FLAG”:true,
“SCORE”:-1,
“OPINION”:“issues”,
“NLP_QUALIFIER”:“MMM”,
“MODIFIED_TIME”:“2014-08-30 10:58:15”,
“CREATED_BY”:"npaaru@bfountain.com”,
“CONTENT”:“My issues with him are pricing and possibly excessive treatment. “,
“WEIGHT”:1,
“ID”:992557,
“EXPERIENCE_TIME”:“2008-06-07 12:00:00”,
“MODIFIED_BY”:"npaaru@bfountain.com”,
“CATEGORY_ID”:4038,
“ANALYSIS_TIME”:“2014-03-11 12:00:00”,
“FEATURE”:“excessive treatment”,
“CREATED_TIME”:“2014-03-11 11:27:54”
},
{
“GROUP_ID”:“npaaru@bfountain.com_1394537274472”,
“CONTENT_ID”:2175967,
“ACTIVE_FLAG”:true,
“SCORE”:-1,
“OPINION”:“wrote off”,
“NLP_QUALIFIER”:“MMM”,
“MODIFIED_TIME”:“2014-08-30 10:58:15”,
“CREATED_BY”:"npaaru@bfountain.com”,
“CONTENT”:“After 5 years of extensive treatment, he still found the gum pockets of 3-4mm which he wrote off as anatomical. “,
“WEIGHT”:1,
“ID”:992558,
“EXPERIENCE_TIME”:“2008-06-07 12:00:00”,
“MODIFIED_BY”:"npaaru@bfountain.com”,
“CATEGORY_ID”:4015,
“ANALYSIS_TIME”:“2014-03-11 12:00:00”,
“FEATURE”:“anatomical”,
“CREATED_TIME”:“2014-03-11 11:30:32”
},
{
“GROUP_ID”:“npaaru@bfountain.com_1394537432572”,
“CONTENT_ID”:2175967,
“ACTIVE_FLAG”:true,
“SCORE”:-1,
“OPINION”:“kept”,
“NLP_QUALIFIER”:“MMM”,
“MODIFIED_TIME”:“2014-08-30 10:58:15”,
“CREATED_BY”:"npaaru@bfountain.com”,
“CONTENT”:“It seems they were expecting to get 1-2mm max. While there’s nothing wrong with the diagnosis, they repeated treatments and kept me on a periodontal 4x / year cleaning schedule that insurance would not fully cover. “,
“WEIGHT”:1,
“ID”:992559,
“EXPERIENCE_TIME”:“2008-06-07 12:00:00”,
“MODIFIED_BY”:"npaaru@bfountain.com”,
“CATEGORY_ID”:4015,
“ANALYSIS_TIME”:“2014-03-11 12:00:00”,
“FEATURE”:“periodontal 4x / year cleaning”,
“CREATED_TIME”:“2014-03-11 11:30:58”
},
{
“GROUP_ID”:“npaaru@bfountain.com_1394537458853”,
“CONTENT_ID”:2175967,
“ACTIVE_FLAG”:true,
“SCORE”:1,
“OPINION”:“polite”,
“NLP_QUALIFIER”:“MMM”,
“MODIFIED_TIME”:“2014-09-26 03:43:41”,
“CREATED_BY”:"npaaru@bfountain.com”,
“CONTENT”:“Billings even for covered service were always in excess of insurance rates. While he and his staff are polite and capable, if you want to use him, be aware that you will pay a lot out of pocket for his service. “,
“WEIGHT”:1,
“ID”:992560,
“EXPERIENCE_TIME”:“2008-06-07 12:00:00”,
“MODIFIED_BY”:"jvarghese@gmail.com”,
“CATEGORY_ID”:4019,
“ANALYSIS_TIME”:“2014-03-11 12:00:00”,
“FEATURE”:“doctor”,
“CREATED_TIME”:“2014-03-11 11:31:19”
},
{
“GROUP_ID”:“npaaru@bfountain.com_1394537479228”,
“CONTENT_ID”:2175967,
“ACTIVE_FLAG”:true,
“SCORE”:1,
“OPINION”:“capable”,
“NLP_QUALIFIER”:“MMM”,
“MODIFIED_TIME”:“2014-08-30 10:58:15”,
“CREATED_BY”:"npaaru@bfountain.com”,
“CONTENT”:“Billings even for covered service were always in excess of insurance rates. While he and his staff are polite and capable, if you want to use him, be aware that you will pay a lot out of pocket for his service. “,
“WEIGHT”:1,
“ID”:992561,
“EXPERIENCE_TIME”:“2008-06-07 12:00:00”,
“MODIFIED_BY”:"npaaru@bfountain.com”,
“CATEGORY_ID”:4021,
“ANALYSIS_TIME”:“2014-03-11 12:00:00”,
“FEATURE”:“staff”,
“CREATED_TIME”:“2014-03-11 11:31:38”
},
{
“GROUP_ID”:“npaaru@bfountain.com_1394537498281”,
“CONTENT_ID”:2175967,
“ACTIVE_FLAG”:true,
“SCORE”:-1,
“OPINION”:“aware”,
“NLP_QUALIFIER”:“MMM”,
“MODIFIED_TIME”:“2014-08-30 10:58:15”,
“CREATED_BY”:"npaaru@bfountain.com”,
“CONTENT”:“Billings even for covered service were always in excess of insurance rates. While he and his staff are polite and capable, if you want to use him, be aware that you will pay a lot out of pocket for his service. “,
“WEIGHT”:1,
“ID”:992562,
“EXPERIENCE_TIME”:“2008-06-07 12:00:00”,
“MODIFIED_BY”:"npaaru@bfountain.com”,
“CATEGORY_ID”:4035,
“ANALYSIS_TIME”:“2014-03-11 12:00:00”,
“FEATURE”:“pay a lot”,
“CREATED_TIME”:“2014-03-11 11:32:19”
},
{
“GROUP_ID”:“npaaru@bfountain.com_1394536948561”,
“CONTENT_ID”:2175967,
“ACTIVE_FLAG”:true,
“SCORE”:1,
“OPINION”:“technically”,
“NLP_QUALIFIER”:“MMM”,
“MODIFIED_TIME”:“2015-11-20 11:42:20”,
“CREATED_BY”:"jvarghese3@bfountain.com”,
“CONTENT”:“I underwent multiple deep scale cleanings and followed his recommendations for at home care, including electric toothbrush and rinse, besides of course daily flossing. He and his staff are technically proficient from what I can judge. “,
“WEIGHT”:0.07,
“ID”:10523566,
“EXPERIENCE_TIME”:“2008-06-07 12:00:00”,
“MODIFIED_BY”:"jvarghese@bfountain.com”,
“CATEGORY_ID”:4408,
“ANALYSIS_TIME”:“2014-03-11 12:00:00”,
“FEATURE”:“proficient”,
“CREATED_TIME”:“2015-05-13 01:33:42”
},
{
“GROUP_ID”:“npaaru@bfountain.com_1394537274472”,
“CONTENT_ID”:2175967,
“ACTIVE_FLAG”:true,
“SCORE”:-1,
“OPINION”:“wrote off”,
“NLP_QUALIFIER”:“MMM”,
“MODIFIED_TIME”:“2015-11-20 11:42:20”,
“CREATED_BY”:"jvarghese3@bfountain.com”,
“CONTENT”:“After 5 years of extensive treatment, he still found the gum pockets of 3-4mm which he wrote off as anatomical. “,
“WEIGHT”:0.07,
“ID”:10523567,
“EXPERIENCE_TIME”:“2008-06-07 12:00:00”,
“MODIFIED_BY”:"jvarghese@bfountain.com”,
“CATEGORY_ID”:4408,
“ANALYSIS_TIME”:“2014-03-11 12:00:00”,
“FEATURE”:“anatomical”,
“CREATED_TIME”:“2015-05-13 01:33:42”
},
{
“GROUP_ID”:“npaaru@bfountain.com_1394537432572”,
“CONTENT_ID”:2175967,
“ACTIVE_FLAG”:true,
“SCORE”:-1,
“OPINION”:“kept”,
“NLP_QUALIFIER”:“MMM”,
“MODIFIED_TIME”:“2015-11-20 11:42:20”,
“CREATED_BY”:"jvarghese3@bfountain.com”,
“CONTENT”:“It seems they were expecting to get 1-2mm max. While there’s nothing wrong with the diagnosis, they repeated treatments and kept me on a periodontal 4x / year cleaning schedule that insurance would not fully cover. “,
“WEIGHT”:0.07,
“ID”:10523568,
“EXPERIENCE_TIME”:“2008-06-07 12:00:00”,
“MODIFIED_BY”:"jvarghese@bfountain.com”,
“CATEGORY_ID”:4408,
“ANALYSIS_TIME”:“2014-03-11 12:00:00”,
“FEATURE”:“periodontal 4x / year cleaning”,
“CREATED_TIME”:“2015-05-13 01:33:42”
},
{
“GROUP_ID”:“npaaru@bfountain.com_1394537458853”,
“CONTENT_ID”:2175967,
“ACTIVE_FLAG”:true,
“SCORE”:1,
“OPINION”:“polite”,
“NLP_QUALIFIER”:“MMM”,
“MODIFIED_TIME”:“2015-05-13 07:03:42”,
“CREATED_BY”:"jvarghese3@bfountain.com”,
“CONTENT”:“Billings even for covered service were always in excess of insurance rates. While he and his staff are polite and capable, if you want to use him, be aware that you will pay a lot out of pocket for his service. “,
“WEIGHT”:0.0763,
“ID”:10523569,
“EXPERIENCE_TIME”:“2008-06-07 12:00:00”,
“MODIFIED_BY”:"jvarghese3@bfountain.com”,
“CATEGORY_ID”:4401,
“ANALYSIS_TIME”:“2014-03-11 12:00:00”,
“FEATURE”:“doctor”,
“CREATED_TIME”:“2015-05-13 01:33:42”
},
{
“GROUP_ID”:“npaaru@bfountain.com_1394537247428”,
“CONTENT_ID”:2175967,
“ACTIVE_FLAG”:true,
“SCORE”:-1,
“OPINION”:“issues”,
“NLP_QUALIFIER”:“MMM”,
“MODIFIED_TIME”:“2015-05-13 07:03:42”,
“CREATED_BY”:"jvarghese3@bfountain.com”,
“CONTENT”:“My issues with him are pricing and possibly excessive treatment. “,
“WEIGHT”:0.0763,
“ID”:10523570,
“EXPERIENCE_TIME”:“2008-06-07 12:00:00”,
“MODIFIED_BY”:"jvarghese3@bfountain.com”,
“CATEGORY_ID”:4407,
“ANALYSIS_TIME”:“2014-03-11 12:00:00”,
“FEATURE”:“excessive treatment”,
“CREATED_TIME”:“2015-05-13 01:33:42”
}
],
“CREATED_BY”:"admin@bfountain.com”,
“PHI_FLAG”:true,
“RESPONSE”:“Dr. Schwartzman was my dentist for 5 years. He initially diagnosed me with periodontal disease due to gum pockets of 4-5mm. I underwent multiple deep scale cleanings and followed his recommendations for at home care, including electric toothbrush and rinse, besides of course daily flossing. \r\n \r\nHe and his staff are technically proficient from what I can judge. My issues with him are pricing and possibly excessive treatment. After 5 years of extensive treatment, he still found the gum pockets of 3-4mm which he wrote off as anatomical. It seems they were expecting to get 1-2mm max. \r\n \r\nWhile there’s nothing wrong with the diagnosis, they repeated treatments and kept me on a periodontal 4x / year cleaning schedule that insurance would not fully cover. Billings even for covered service were always in excess of insurance rates. \r\n \r\nWhile he and his staff are polite and capable, if you want to use him, be aware that you will pay a lot out of pocket for his service. You may want a second opinion to be sure there is not intentional overcharging for unnecessary service.”,
“ID”:2175967,
“MODELS”:“phi”,
“MODIFIED_BY”:"thread_nlp4@bfountain.com",
“WORD_COUNT”:192,
“NLP_FLAG”:true,
“CREATED_TIME”:“2014-03-11 09:18:29”
}
],
“REVIEW_COUNT”:1,
“LOCATION_ID”:0,
“OWNER_POST_FLAG”:false,
“PERSON_ID”:11489,
“RECOMMEND_FLAG”:true,
“HARVESTER_ID”:74177,
“CREATED_TIME”:“2014-03-11 02:48:29”
}

so basically i need the average score if category_id in [4026,4017];
so i make the below query

select avg(CONTENTS[1].SENTIMENTS[0].SCORE) from default use keys “123456” where CONTENTS[1].ID=2175967 AND ANY SR IN CONTENTS[1].SENTIMENTS[0:9]
SATISFIES SR.CATEGORY_ID IN [4015] END;
but i’m getting the result 0
[
{
"$1": 0
}
]. could you help me where i’m dong wrong here.

geraldss · August 8, 2016, 5:51pm

Remove the “use keys” in your query.

rkumar · August 9, 2016, 5:21am

Hi ,

Thank you for your reply.

but my ultimate goal is to get the average score based on category_id in sentiments array.

so i wrote this:

SELECT avg(c.SCORE)
FROM default t UNNEST t.CONTENTS[1].SENTIMENTS c
WHERE c.CATEGORY_ID IN [4015,4021]
and its working fine for me,but this query is a catch,that i’m getting the sentiments from CONTENTS[1].SENTIMENTS

but its very much possibilities that CONTENTS[0].SENTIMENTS may also have sentiments.

so its a great if you help me to sort this out.

geraldss · August 9, 2016, 5:57pm

How about this. I am not familiar with your data or requirements, so you need to verify the query and results against many tests.

SELECT avg(s.SCORE)
FROM default t
UNNEST t.CONTENTS c
UNNEST c.SENTIMENTS s
WHERE s.CATEGORY_ID IN [4015,4021];

rkumar · August 10, 2016, 6:24am

Hi Gerald,

Thank you for your reply and work,its a great.

this N1QL working for me but still let me aware of our requirement and documents format,so that you can help us in convenient way

we create json document in below format.

we have json having three different types,

TYPE=“DOCUMENT”
TYPE=“CONTENT”
TYPE=“SENTIMENT”

so our json have below structure,

one Document can have–> more than 1 Content and each Content–> have more than 1 sentiment and sentiment have score=1/0/-1

,1=positive,0=neutral and -1 =negative respt.

so they merge like below: i attached the json document for better view.

D1{
C1[
{S1–having Category_id=4000 & Score=1},
{S2 -having Category_id=4024& Score=1}
],
C2[
{ S1 -having Category_id=4000 & Score=-1},
{ S2 -having Category_id=4024 & Score=0 },
{ S3-having Category_id=4501& Score=1 }
]
}

Requirement having:

avg(score) D1 with category id in (4000,4024)-

apart of this we looking for performance as well because we have 2,00,000 of records in one bucket.

It will be a great help if you guide me on this.

Thanks & Regards,

Rahul

geraldss · August 10, 2016, 4:29pm

Sounds good. Implement and test a working solution, and then we can review here. Go through the full N1QL documentation and take the online course. They are your friends.

rkumar · August 22, 2016, 6:11am

Hi Geraldss,

Thank you for above support.
i have another issue in create index part, now i have 10Lacs document in my server and i need the average score with below query which take much more time that we can’t afford.

SELECT ROUND(AVG(s.SCORE),1)
FROM default t
UNNEST t.CONTENTS c
UNNEST c.SENTIMENTS s
WHERE s.CATEGORY_ID IN [4015,4012,4021…];

so i create index on this
create index idx_score on default (ACTIVE_FLAG,(distinct array s.SCORE AND s.CATEGORY_ID for s in SENTIMENTS end))
where TYPE=“document”;
but still i don’ get noticable speedup.
could you please help me here,whether i make a right indexing or not,ot it should optimized.

I JUST saw that it don’t take newly created index in this query

AFTER R&D, i wrote below index
CREATE INDEX idx_score ON default
(DISTINCT ARRAY
(DISTINCT ARRAY s.SCORE FOR s IN c.SENTIMENTS END)
FOR c IN CONTENTS END) WHERE TYPE=DOCUMENT";

and the query is:
select AVG(s.SCORE) from default d
UNNEST d.CONTENTS c
UNNEST c.SENTIMENTS s
WHERE s.CATEGORY_ID IN [4015] AND d.MENTION_TIME>=‘2010-01-01’;
bu EXPLAIN show that it doesn’t using this index,rather it take up primary index.
please help me here

geraldss · August 22, 2016, 8:44pm

Your query also needs:

AND d.TYPE=“document”

rkumar · August 23, 2016, 11:53am

Hi,
yes that i forgot to mention.

how covering index with multiples attribute work in create index,below is the doc structure:
{

“SURVEY_TYPE”:“FEEDBACK”,
“MENTION_TIME”:“2008-06-07 05:30:00”,
“ID”:21206,
“ACTIVE_FLAG”:true,
“SOURCE_ID”:100115,
“CONTENTS”:[

  { 
     "ACTIVE_FLAG":true, 
     "PREDEFINED":false, 
     "MD5":"911d10c91c6c80afb0a1a4d4ab786d24", 
     "QUESTION_ID":1000000000, 
     "STATUS":"P", 
     "PUBLISHING_API_STATUS":"N", 
     "OPTION_ID":1, 
     "MODIFIED_TIME":"2015-08-18 10:31:08", 
     "SENTIMENTS":[ 
        { 
           "GROUP_ID":"npaaru@bfountain.com_1394536948561", 
           "CONTENT_ID":2175967, 
           "ACTIVE_FLAG":true, 
           "SCORE":1, 
          
        } 
       
     ], 
    
     "NLP_FLAG":true, 
     "CREATED_TIME":"2014-03-11 09:18:29" 
  }

],
“REVIEW_COUNT”:1,
“LOCATION_ID”:0,
“OWNER_POST_FLAG”:false,
“PERSON_ID”:11489,
“RECOMMEND_FLAG”:true,
“HARVESTER_ID”:74177,

}
so i CREATE THE INDEX LIKE BELOW:

CREATE INDEX idx_score_adv ON default
(PERSON_ID,SURVEY_TYPE,LOCATION_ID,SOURCE_ID,
(DISTINCT ARRAY (DISTINCT ARRAY s.SCORE AND s.CATEGORY_ID FOR s IN c.SENTIMENTS END) FOR c IN CONTENTS END)) WHERE MENTION_TIME>=‘2008-01-01’ AND SURVEY_TYPE=“FEEDBACK”;

so i wrote below query,which should took this index but explain show difference:

Query1:
EXPLAIN SELECT round(avg( s.SCORE),1) as score FROM default t
UNNEST t.CONTENTS c
UNNEST c.SENTIMENTS s
WHERE t.SURVEY_TYPE=“FEEDBACK” AND s.CATEGORY_ID IS NOT NULL and t.MENTION_TIME >=‘2010-01-01’ and t.MENTION_TIME<‘2015-01-01’ ;

EXPLAIN:
[
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
** “#operator”: “PrimaryScan”,**
** “index”: “#primary”,**
** “keyspace”: “default”,**
** “namespace”: “default”,**
** “using”: “gsi”**
** },**
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Fetch”,
“as”: “t”,
“keyspace”: “default”,
“namespace”: “default”
},
{
"#operator": “Unnest”,
“as”: “c”,
“expr”: “(t.CONTENTS)”
},
{
"#operator": “Unnest”,
“as”: “s”,
“expr”: “(c.SENTIMENTS)”
},
{
"#operator": “Filter”,
“condition”: “(((((t.SURVEY_TYPE) = “FEEDBACK”) and ((s.CATEGORY_ID) is not null)) and (“2010-01-01” <= (t.MENTION_TIME))) and ((t.MENTION_TIME) < “2015-01-01”))”
},
{
"#operator": “InitialGroup”,
“aggregates”: [
“avg((s.SCORE))”
],
“group_keys”: []
}
]
}
},
{
"#operator": “IntermediateGroup”,
“aggregates”: [
“avg((s.SCORE))”
],
“group_keys”: []
},
{
"#operator": “FinalGroup”,
“aggregates”: [
“avg((s.SCORE))”
],
“group_keys”: []
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “InitialProject”,
“result_terms”: [
{
“as”: “score”,
“expr”: “round(avg((s.SCORE)), 1)”
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
“text”: “SELECT round(avg( s.SCORE),1) as score FROM default t \nUNNEST t.CONTENTS c \nUNNEST c.SENTIMENTS s \nWHERE t.SURVEY_TYPE=“FEEDBACK” AND s.CATEGORY_ID IS NOT NULL and t.MENTION_TIME >=‘2010-01-01’ and t.MENTION_TIME<‘2015-01-01’ ;”
}
]

But In Query2 id i add PERSON_ID,story differ:

EXPLAIN SELECT round(avg( s.SCORE),1) as score FROM default t USE INDEX (idx_score_adv_one)
UNNEST t.CONTENTS c
UNNEST c.SENTIMENTS s
WHERE t.SURVEY_TYPE=“FEEDBACK” AND t.PERSON_ID=11489 AND t.LOCATION_ID IS NOT NULL and t.MENTION_TIME >=‘2010-01-01’ and t.MENTION_TIME<‘2015-01-01’ ;

EXPLAIN:
[
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
** “#operator”: “IndexScan”,**
** “index”: “idx_score_adv_one”,**
** “index_id”: “c165d6492d2ef1f8”,**
** “keyspace”: “default”,**
** “namespace”: “default”,**
** “spans”: [**
{
“Range”: {
“High”: [
“11489”,
“successor(“FEEDBACK”)”
],
“Inclusion”: 0,
“Low”: [
“11489”,
"“FEEDBACK”",
“null”
]
}
}
],
“using”: “gsi”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Fetch”,
“as”: “t”,
“keyspace”: “default”,
“namespace”: “default”
},
{
"#operator": “Unnest”,
“as”: “c”,
“expr”: “(t.CONTENTS)”
},
{
"#operator": “Unnest”,
“as”: “s”,
“expr”: “(c.SENTIMENTS)”
},
{
"#operator": “Filter”,
“condition”: “((((((t.SURVEY_TYPE) = “FEEDBACK”) and ((t.PERSON_ID) = 11489)) and ((t.LOCATION_ID) is not null)) and (“2010-01-01” <= (t.MENTION_TIME))) and ((t.MENTION_TIME) < “2015-01-01”))”
},
{
"#operator": “InitialGroup”,
“aggregates”: [
“avg((s.SCORE))”
],
“group_keys”: []
}
]
}
},
{
"#operator": “IntermediateGroup”,
“aggregates”: [
“avg((s.SCORE))”
],
“group_keys”: []
},
{
"#operator": “FinalGroup”,
“aggregates”: [
“avg((s.SCORE))”
],
“group_keys”: []
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “InitialProject”,
“result_terms”: [
{
“as”: “score”,
“expr”: “round(avg((s.SCORE)), 1)”
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
“text”: “SELECT round(avg( s.SCORE),1) as score FROM default t USE INDEX (idx_score_adv_one)\nUNNEST t.CONTENTS c \nUNNEST c.SENTIMENTS s \nWHERE t.SURVEY_TYPE=“FEEDBACK” AND t.PERSON_ID=11489 AND t.LOCATION_ID IS NOT NULL and t.MENTION_TIME >=‘2010-01-01’ and t.MENTION_TIME<‘2015-01-01’ ;”
}
]
s will you explain how this work,or what i’m missing here.

Thank you sir.

geraldss · August 25, 2016, 5:20pm

Move PERSON_ID and SOURCE_ID to the end of the index, or remove them from the index altogether.

Remember, the prefix of the index must match the query.

Topic		Replies	Views
N1QL help with nested array SQL++	4	276	June 25, 2023
N1QL query to filter and search for two nested array of objects in the parent objects SQL++ n1ql	5	1393	November 23, 2022
N1QL Access different indices of Array in JSON SQL++	3	2634	December 30, 2014
How to get an array of objects using n1ql query SQL++ query , n1ql	1	2638	December 10, 2019
Q: object connect query with N1QL SQL++ query	9	905	July 29, 2019

How to iterate two object of array on condition ,outer array inside it inner array on condition

SELECT ROUND(AVG(s.SCORE),1) FROM default t UNNEST t.CONTENTS c UNNEST c.SENTIMENTS s WHERE s.CATEGORY_ID IN [4015,4012,4021…];

I JUST saw that it don’t take newly created index in this query

Related topics

SELECT ROUND(AVG(s.SCORE),1)
FROM default t
UNNEST t.CONTENTS c
UNNEST c.SENTIMENTS s
WHERE s.CATEGORY_ID IN [4015,4012,4021…];