Getting document structure

SQL++
1

Hi
Supposing i have such document

 {
      "f": "s",
      "field1": "123",
      "field2": {
        "field21": Null,
        "field22": ["a", "b", 5525],
        "field23": {
          "field221": "field221data"
        }

How can i get a listof pairs (“field name” + "type ") like this:

{
       "f": string,
      "field1": int,
      "field2": Object
 }
2 
SELECT RAW OBJECT f:type(v) FOR f:v IN { "f": "s", "field1": 123,  "field21": Null, "field22": ["a", "b", 5525], "field23": { "field221": "field221data" }, "b":true} END;
{
    "requestID": "71d6c22c-176e-4f2f-88de-978be8ea65bb",
    "signature": "object",
    "results": [
    {
        "b": "boolean",
        "f": "string",
        "field1": "number",
        "field21": "null",
        "field22": "array",
        "field23": "object"
    }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "1.368997ms",
        "executionTime": "1.309763ms",
        "resultCount": 1,
        "resultSize": 165,
        "serviceLoad": 2
    }
}
1 Like
3

Depending on the problem you’re trying to solve, the N1QL INFER statement might also be useful.

1 Like
4

Thanks!

I’ve been trying to scan a range of documents with this so i wrote such query

WITH var1 AS (
    SELECT RAW OBJECT f:TYPE(v) FOR f:v IN b END
    FROM DOCS b USE INDEX (DOCS_IDX)
    WHERE META(b).id LIKE "Document_test%"
     ),
var2 AS (
    SELECT DISTINCT VALUE ARRAY_FLATTEN((ARRAY OBJECT_PAIRS(var1[v]) FOR v IN ARRAY_RANGE(0, ARRAY_COUNT(var1), 1) END),3))
SELECT VALUE ARRAY_DISTINCT(ARRAY_FLATTEN(var2,8))

It worked fine with small range of documents but keept throwing “Out of range evaluating ARRAY_RANGE” error , so i wrote another version

WITH var1 AS (
    SELECT RAW OBJECT f:TYPE(v) FOR f:v IN b END
    FROM DOCS b USE INDEX (DOCS_IDX)
    WHERE META(b).id LIKE "Document_test%"
     ),
ar1 AS (ARRAY_RANGE(0, (ARRAY_COUNT(var1))/2, 1)),
ar3 AS (ARRAY_CONCAT(ARRAY_RANGE(0, (ARRAY_COUNT(var1))/2, 1), ARRAY_RANGE((ARRAY_COUNT(ar1)),(ARRAY_COUNT(var1)), 1))),
var2 AS (
    SELECT DISTINCT VALUE ARRAY_FLATTEN((ARRAY OBJECT_PAIRS(var1[v]) FOR v IN ar3 END),3))
SELECT VALUE ARRAY_DISTINCT(ARRAY_FLATTEN(var2,8))

But it also works quite unstable with big amount of documents

Could you please tell me how to make this query more effective?

5

@Kessler2009 ,

 SELECT DISTINCT  ub.name,  type(ub.val) AS type
 FROM DOCS b USE INDEX (DOCS_IDX)
UNNEST OBJECT_PAIRS(b) AS ub
WHERE META(b).id LIKE "Document_test%"

SELECT  DISTINCT RAW  ub
FROM DOCS b USE INDEX (DOCS_IDX)
UNNEST (ARRAY {"name": f , "val":TYPE(v)} FOR f:v IN b END) AS ub
WHERE META(b).id LIKE "Document_test%";

OBJECT_PAIRS(OBJECT f:TYPE(v) FOR f:v IN b END) 
 ARRAY {"name": f , "val":TYPE(v)} FOR f:v IN b END  both same

You will not able to construct OBJECT because you are doing across object name can have different type and object needs unique name.

1 Like