AlaSQL.js WHERE Clause on a Feld that is an Array of Objects

Question

What I'm trying to do is SELECT metadata->name, metadata->namespace but only if the spec.volumes contains an object with secretName = 'test-secret' from a list of objects that look like this:

{
  "kind": "PodList",
  "apiVersion": "v1",
  "metadata": {
    "selfLink": "/api/v1/pods",
  },
  "items": [
    {
      "metadata": {
        "name": "pod1",
        "namespace": "namespace1"
      },
      "spec": {
        "volumes": [
          {
            "name": "default-token",
            "secret": {
              "secretName": "default-token"
            }
          }
        ],
        "nodeName": "node-1"
      }
    }
    {
      "metadata": {
        "name": "pod2",
        "namespace": "namespace2"        
      },
      "spec": {
        "volumes": [
          {
            "name": "default-token",
            "secret": {
              "secretName": "default-token"
            }
          }
        ],
        "nodeName": "node-2"
      }
    },
    {
      "metadata": {
        "name": "chosen-pod",
        "namespace": "namespace3",
      },
      "spec": {
        "volumes": [
          {
            "name": "pod-storage",
          },
          {
            "name": "test-data",
            "secret": {
              "secretName": "test-secret"
            }
          }
        ],
        "nodeName": "node-2",
      }
    }
  ]
}

This list is inserted into a table;

CREATE TABLE pods (node TEXT, metadata Object, spec Object)

I think this is the closest I've come;

SELECT metadata->name, metadata->namespace, spec->volumes AS volumes FROM pods WHERE (SELECT COUNT(*) FROM ? WHERE secret->secretName = 'test-data') > 0

But this throws an error:

TypeError: Cannot read property '0' of undefined
    at Object.eval [as datafn] (eval at <anonymous> (~/Documents/myproject/node_modules/alasql/dist/alasql.fs.js:7487:20), <anonymous>:3:40)
    at ~/Documents/myproject/node_modules/alasql/dist/alasql.fs.js:6100:19
    at Array.forEach (native)
    at queryfn (~/Documents/myproject/node_modules/alasql/dist/alasql.fs.js:6097:16)
    at Array.statement (~/Documents/myproject/node_modules/alasql/dist/alasql.fs.js:7121:14)
    at eval [as wherefn] (eval at <anonymous> (~/Documents/myproject/node_modules/alasql/dist/alasql.fs.js:7899:11), <anonymous>:3:56)
    at doJoin (~/Documents/myproject/node_modules/alasql/dist/alasql.fs.js:6615:12)
    at doJoin (~/Documents/myproject/node_modules/alasql/dist/alasql.fs.js:6687:8)
    at queryfn3 (~/Documents/myproject/node_modules/alasql/dist/alasql.fs.js:6174:2)
    at queryfn2 (~/Documents/myproject/node_modules/alasql/dist/alasql.fs.js:6149:9)

Hmm, is there an error in my WHERE clause? No, removing > 0 from the end and it throws the same exception.

Working backwards from this question/ answer I also tried;

SEARCH / AS @a
  UNION ALL(
    spec->volumes AS @v
    RETURN(@a->metadata->name AS name, 
      @a->metadata->namespace AS namespace, 
      @v->secret->secretName AS [secretNames]
    )
  ) 
FROM pods

But the @v->secret->secretName AS [secretNames] just yields undefined;

[ 'pod1', 'namespace1', undefined ]
[ 'pod2', 'namespace2', undefined ]
[ 'chosen-pod', 'namespace3', undefined ]

Returning the naked @v array puts me right back where I was before--a row with an array that I can't seem to filter against.

agershun · Accepted Answer · 2016-08-20 13:58:39Z

1

The problem is in spec->volumes. It is an array, so you need to change the query to @v->0->secret->secretName:

SEARCH / AS @a
  UNION ALL(
    spec->volumes AS @v
    RETURN(@a->metadata->name AS name, 
      @a->metadata->namespace AS namespace, 
      @v->0->secret->secretName AS [secretNames]
    )
  ) 
FROM pods

To extract the required elements, please try this code:

const data = {...data...};
let res = alasql('SEARCH items / AS @a     \
    spec volumes / WHERE(name="test-data") \
    RETURN(@a->metadata->name AS name,     \
      @a->metadata->namespace AS namespace \
    ) FROM ?',[data]);

Here spec volumes / loop over all elements of array spec.volumes[].

edited Aug 20, 2016 at 13:58

answered Aug 19, 2016 at 9:16

agershun

4,10742 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

pnovotnak Over a year ago

tickled pink that I'm talking to the author himself! Yeah, querying the first element works in both queries, but is there a way to query all of the sub-objects at once?

agershun Over a year ago

Sorry, I missed the point. I changed the query and updated the answer. I returns only elements where there are elements with name = 'test-data'

Collectives™ on Stack Overflow

AlaSQL.js WHERE Clause on a Feld that is an Array of Objects

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related