N1QL to fetch by path levels

SQL++
1

Hi,

I have a hierarchy document which was created using subdoc API.
Need to query this document to fetch one level at a time.
The structure looks similar to the below:

{  
  "root_path":{  
    "level_1":{  
      "level_2":{  
        "field1":"value1",
        "field2":"value2",
        "field3":"value3",
        "level_3": {
              "field1":"value1",
              "field2":"value2",
              "field3":"value3"
              }
      },
      "field1":"value1",
      "field2":"value2",
      "field3":"value3"
    }
  }
}

Please note that “level_1” or "level_2"or not fixed and are dynamic.
Please help me with the N1QL query.
Appreciate any help :slight_smile:

2

is this what you want?

SELECT OBJECT_UNWRAP(OBJECT_UNWRAP(default)) FROM default ;

the result:

[
  {
    "$1": {
      "field1": "value1",
      "field2": "value2",
      "field3": "value3",
      "level_2": {
        "field1": "value1",
        "field2": "value2",
        "field3": "value3",
        "level_3": {
          "field1": "value1",
          "field2": "value2",
          "field3": "value3"
        }
      }
    }
  }
]
3

Appreciate your help @atom_yang.
Not quite what I was looking for.
Basically need to be able to filter by path levels… e.g. fetch all first levels or fetch all third levels etc…
Is this kind of operation supported?

4

may be you can try OBJECT_VALUES function or other OBJECT function.

5

Hi,

Can you provide the following:

(1) Sample input document
(2) Sample output for level_1 query
(3) Sample output for level_2 query

6 
Apologies for not replying to earlier.

sample input document looks like below:
    {  
      "organisation":{  
        "Engineering":{  
          "Research":{  
            "field1":"value1",
            "field2":"value2",
            "field3":"value3",
            "Development":{  
              "field1":"value1",
              "field2":"value2",
              "field3":"value3"
            }
          },
          "field1":"value1",
          "field2":"value2",
          "field3":"value3"
        },
        "HR":{  
          "Payroll":{  
            "field1":"value1",
            "field2":"value2",
            "field3":"value3",
            "Performance":{  
              "field1":"value1",
              "field2":"value2",
              "field3":"value3"
            }
          },
          "field1":"value1",
          "field2":"value2",
          "field3":"value3"
        }
      }
    }


Sample output for level_1 query to include only level_1 nodes i.e., in this case should be
     {  
      "Engineering":{  
        "field1":"value1",
        "field2":"value2",
        "field3":"value3"
      },
      "HR":{  
        "field1":"value1",
        "field2":"value2",
        "field3":"value3"
      }
    }


Sample output for level_3 query 
    {  
      "Development":{  
        "field1":"value1",
        "field2":"value2",
        "field3":"value3"
      },
      "Performance":{  
        "field1":"value1",
        "field2":"value2",
        "field3":"value3"
      }
    }

Thanks in advance.
7

Spock allows you to select from an expression - so you can use object_pairs() to unwind levels into an array,
and unnest to the elements, e.g. this gives you the first levels:

suppose you have

insert into default values("1", 
    { 'level1_1': { 'level2_a': { 'a': 1, 'b': 2 }},
      'level1_2': { 'level2_b': { 'c': 3, 'd': 4 }}})

you could

select level1.val
  from (select object_pairs(default) level1
          from default) level0
            unnest level0.level1

This produces

[
  {
    "val": {
      "level2_a": {
        "a": 1,
        "b": 2
      }
    }
  },
  {
    "val": {
      "level2_b": {
        "c": 3,
        "d": 4
      }
    }
  }
]

Is this the sort of thing you’d like to achieve?

1 Like
8

We’ve created a feature request to implement a generalized solution for this. Please follow this: https://issues.couchbase.com/browse/MB-24067

1 Like
9

Pretty close… Thank you :slight_smile:

10

Will be a nice feature to have and I will be looking forward for it.
Thank you @keshav_m .