How to Select' field's in list N1QL

SQL++
1

Hi,
I have a document that looks like this:

"host1": {
    "instances": [
      {
        "capacity": {
          "change": {
            "rps": 0,
            "rt": 1000
          },
          "max_connections": 0,
          "read": {
            "rps": 0,
            "rt": 500
          }
        },
        "conn_string": "local",
        "state": "enabled"
      }
    ],
    "quotas": {
      "activities": {
        "redirect": {
          "change": 100,
          "read": 100
        },
        "lost": {
          "change": 0,
          "read": 1
        }
      },
      "users": {
        "default": {
          "change": 0,
          "max_connections": 0,
          "read": 0
        },
        "admin": {
          "change": 0,
          "max_connections": 1,
          "read": 1
        }
      }
    }
  },
  "host2": {
    "instances": [
      {
        "capacity": {
          "change": {
            "rps": 0,
            "rt": 1000
          },
          "max_connections": 0,
          "read": {
            "rps": 0,
            "rt": 500
          }
        },
        "conn_string": "local",
        "state": "enabled"
      }
    ],
    "quotas": {
      "activities": {
        "redirect": {
          "change": 100,
          "read": 100
        },
        "lost": {
          "change": 0,
          "read": 1
        }
      },
      "users": {
        "default": {
          "change": 0,
          "max_connections": 0,
          "read": 0
        },
        "admin": {
          "change": 0,
          "max_connections": 1,
          "read": 1
        }
      }
    }
  },

I want to get fields from host 1 and host 2 conn_string and state.

My query N1QL look like this

SELECT host1.instances.conn_string,instances.state FROM registry USE KEYS ‘source_host’
SELECT host1 FROM registry source_host USE KEYS ‘source_host’
SELECT host1 FROM registry host1 UNNEST instances AS instans WHERE instans.state=‘enabled’
SELECT count(*) FROM registry host1 WHERE ANY v IN instances SATISFIES v.state LIKE ‘enabled%’

How do I get these fields? None of these queries work correctly.

Help solve the problem,

Thanks.

2

If all the data is single document like below

  INSERT INTO default VALUES ("k001",
{ "host1": { "instances": [ { "capacity": { "change": { "rps": 0, "rt": 1000 }, "max_connections": 0, "read": { "rps": 0, "rt": 500 } }, "conn_string": "local", "state": "enabled" } ], "quotas": { "activities": { "redirect": { "change": 100, "read": 100 }, "lost": { "change": 0, "read": 1 } }, "users": { "default": { "change": 0, "max_connections": 0, "read": 0 }, "admin": { "change": 0, "max_connections": 1, "read": 1 } } } }, "host2": { "instances": [ { "capacity": { "change": { "rps": 0, "rt": 1000 }, "max_connections": 0, "read": { "rps": 0, "rt": 500 } }, "conn_string": "local", "state": "enabled" } ], "quotas": { "activities": { "redirect": { "change": 100, "read": 100 }, "lost": { "change": 0, "read": 1 } }, "users": { "default": { "change": 0, "max_connections": 0, "read": 0 }, "admin": { "change": 0, "max_connections": 1, "read": 1 } } } } });

SELECT op.name, opi.conn_string, opi.state
FROM  default AS r USE KEYS "k001"
UNNEST OBJECT_PAIRS(r)  AS op
UNNEST op.val.instances  AS opi
WHERE op.name  IN ["host1", "host2"]  ;

Each host has separate document like below

INSERT INTO default VALUES ("host1", { "instances": [ { "capacity": { "change": { "rps": 0, "rt": 1000 }, "max_connections": 0, "read": { "rps": 0, "rt": 500 } }, "conn_string": "local", "state": "enabled" } ], "quotas": { "activities": { "redirect": { "change": 100, "read": 100 }, "lost": { "change": 0, "read": 1 } }, "users": { "default": { "change": 0, "max_connections": 0, "read": 0 }, "admin": { "change": 0, "max_connections": 1, "read": 1 } } } });
INSERT INTO default VALUES ("host2", { "instances": [ { "capacity": { "change": { "rps": 0, "rt": 1000 }, "max_connections": 0, "read": { "rps": 0, "rt": 500 } }, "conn_string": "local", "state": "enabled" } ], "quotas": { "activities": { "redirect": { "change": 100, "read": 100 }, "lost": { "change": 0, "read": 1 } }, "users": { "default": { "change": 0, "max_connections": 0, "read": 0 }, "admin": { "change": 0, "max_connections": 1, "read": 1 } } } });

SELECT META(r).id AS name, opi.conn_string, opi.state
FROM  default AS r USE KEYS ["host1", "host2"]
UNNEST r.instances  AS opi;
1 Like
3

SELECT op.name, opi.conn_string, opi.state
FROM registry AS r USE KEYS “source_host”
UNNEST OBJECT_PARIS® AS op
UNNEST op.instances AS opi
WHERE op.name IN [“host1”, “host2”] ;

This query return empty array
{
“results”:
}
and the function is called not PARIS but is called PAIRS)l

4

Updated previous post. try again

1 Like
5

Thanks so much, it work’s