How do I get an s3 select query to return individual rows

Question

With data structured like so

{
    "rows": [
        {
            "rowId": "IDP_2z8dfj9KbB1hrPI_1554508960_1_1",
            "version": "1554508960",
            "lastUpdatedDate": 1554508960604,
            "createdAt": 1554508960604,
            "payload": ""
        },
        {
            "rowId": "IDP_2z8dfj9KbB1hrPI_1554508960_1_2",
            "version": "1554508960",
            "lastUpdatedDate": 1554508960604,
            "createdAt": 1554508960604,
            "payload": ""
        },
        {
            "rowId": "IDP_2z8dfj9KbB1hrPI_1554508960_1_3",
            "version": "1554508960",
            "lastUpdatedDate": 1554508960604,
            "createdAt": 1554508960604,
            "payload": ""
        }
    ]
}

The following select query

select * from S3Object s where 'IDP_2z8dfj9KbB1hrPI_1554508960_1_2' in s[*]."rows"[*]."rowId"

returns the entire JSON document, where I would expect it to return only a single row, i.e.

[{
            "rowId": "IDP_2z8dfj9KbB1hrPI_1554508960_1_2",
            "version": "1554508960",
            "lastUpdatedDate": 1554508960604,
            "createdAt": 1554508960604,
            "payload": ""
}]

I have tried several variations of the query

select s.rows[0] from S3Object s where 'IDP_2z8dfj9KbB1hrPI_1554508960_1_2' in s[*]."rows"[*]."rowId"

which returns only 1 record, but it is the wrong record.

select * from S3Object s where  s[*]."rows"[*]."rowId"='IDP_2z8dfj9KbB1hrPI_1554508960_1_2'

returns nothing.

Any ideas on what I'm doing wrong?

score 1 · Answer 1 · answered Apr 07 '19 at 00:02

1

After several more modifications of the query, I was able to get my expected result with

select * from S3Object[*]."rows"[*] as r  where ('IDP_2z8dfj9KbB1hrPI_1554508960_1_1') in r[*]."rowId"

answered Apr 07 '19 at 00:02

Aaron Wilson

130
7

How do I get an s3 select query to return individual rows

1 Answers1