Query on sub-document

SQL++
1

my sample documents are:

{
  "SCP_1": {
    "active": true,
    "jobId": "345",
    "dataSourceCode": "abc",
    "activationTime": "0001-01-01T00:00:00",
    "_type": "Listener"
  },
  "SCP_2": {
    "active": false,
    "jobId": "123",
    "dataSourceCode": "abc",
    "activationTime": "0001-01-01T00:00:00",
    "_type": "Listener"
  }
}
,
{
  "SCP_3": {
    "active": true,
    "jobId": "888",
    "dataSourceCode": "abc",
    "activationTime": "0001-01-01T00:00:00",
    "_type": "Listener"
  },
  "SCP_4": {
    "active": false,
    "jobId": "999",
    "dataSourceCode": "abc",
    "activationTime": "0001-01-01T00:00:00",
    "_type": "Listener"
  }
}

how does my query return  active = true sub-document only? the query result should be:


the result should be :
{
  "SCP_1": {
    "active": true,
    "jobId": "345",
    "dataSourceCode": "abc",
    "activationTime": "0001-01-01T00:00:00",
    "_type": "Listener"
  },
  "SCP_3": {
    "active": true,
    "jobId": "888",
    "dataSourceCode": "abc",
    "activationTime": "0001-01-01T00:00:00",
    "_type": "Listener"
  }
}

Thanks,

2

@Van2008 ,

Assume each one is separate document

CRETAE INDEX ix1 ON bucket(active) WHERE  _type = "Listener";
SELECT t.*
FROM bucket AS t 
WHERE t._type = "Listener" AND t.active = true;

If whole thing is one document and you want get only those nested objects that has active true

CREATE INDEX ix2 ON bucket(DISTINCT ARRAY v.active FOR v IN OBJECT_VALUES(self) END);

SELECT RAW OBJECT n:v FOR n:v IN t WHEN v.active = true END
FROM bucket AS t 
WHERE ANY v IN OBJECT_VALUES(t) SATISFIES v.active =  true END;
3

Thanks for your quick response.

How do I get the result as single document. like:
{
“SCP_1”: {
“active”: true,
“jobId”: “345”,
“dataSourceCode”: “abc”,
“activationTime”: “0001-01-01T00:00:00”,
“_type”: “Listener”
},
“SCP_3”: {
“active”: true,
“jobId”: “888”,
“dataSourceCode”: “abc”,
“activationTime”: “0001-01-01T00:00:00”,
“_type”: “Listener”
}
}

4

Show me exact original document? original post is single document, second query above will get you results;

5

document #1:
{
“SCP_1”: {
“active”: true,
“jobId”: “345”,
“dataSourceCode”: “abc”,
“activationTime”: “0001-01-01T00:00:00”,
“_type”: “Listener”
},
“SCP_2”: {
“active”: false,
“jobId”: “123”,
“dataSourceCode”: “abc”,
“activationTime”: “0001-01-01T00:00:00”,
“_type”: “Listener”
}
}
document #2:
{
“SCP_3”: {
“active”: true,
“jobId”: “888”,
“dataSourceCode”: “abc”,
“activationTime”: “0001-01-01T00:00:00”,
“_type”: “Listener”
},
“SCP_4”: {
“active”: false,
“jobId”: “999”,
“dataSourceCode”: “abc”,
“activationTime”: “0001-01-01T00:00:00”,
“_type”: “Listener”
}
}

6

Getting multiple objects from different document and forming one document can have loss of data because object field names must be unique. How Do u resolve them?

7

in my case, the object filed names are unique. is it possible to form 1 document?

8 
CREATE INDEX ix1 ON bucket(ALL ARRAY u.val.active FOR u IN OBJECT_PAIRS(self) END);
SELECT RAW OBJECT v.name:v.val
           FOR v IN ( SELECT RAW u FROM bucket AS t UNNEST OBJECT_PAIRS(t) AS u WHERE u.val.active = true)
           END;
9

working perfectly! Thank you