Using PartiQL with AWS API JSON

Hello,

I’m trying to use the REPL to query JSON as supplied by the AWS EC2 API, but I’m running into various difficulties. When I do something like describe-instances, I seem to need to translate double-quote into single quote. No big deal.

But then my queries don’t seem to work. I can do select Instances from Reservations, but if I do something like select InstanceId from Reservations.Instances I just get null data. Looking at the data, it seems the difficulty is that Instances is an array.

Anyone done this sort of thing or have some tips?

@mjg here is how you can get the InstanceId:

discribe-instances sample data: Debian Pastezone

PartiQL> SELECT i.instanceId FROM reservations as r, r.instances AS i;
==='
<<
  {
    'instanceId': 'i-1234567890abcdef0'
  }
>>
---
OK!

If you haven’t already, I’d recommended looking into PartiQL Tutorial, esp. unnesting-a-nested-collection section.

I appreciate the response. Not long after my post, with further playing around, I was able to come up with the same result.

The semantics are a little wonky, IMO. But, I also get why its necessary for truly schema-free datasets.

Just getting acclimated, I guess.

Definitely going to keep an eye on this project, but as of now without the DML, I sadly need to continue to rely on a ‘proper’ SQL server. This is annoying, because most of the data in my SQL server is generated out of AWS in the first place, and it would be MUCH better to simply aggregate the JSON output and deal with it directly, rather than having jobs to cut it up and get it into tables.

@mjg if haven’t gone through it already, PartiQL Spec. describes the semantics in details which can help in understanding the PartiQL behavior in different cases. For example, in this case—where we have multiple FROM sources delimited by comma—PartiQL performs a cross join (Cartesian product) of the sources under the hood as an input to the SELECT projection; this has been explained in Section 5.4 of the spec.

As for the DML, there are multiple streams of work to formalize and implement the logic. It’s great that you’re going to keep an eye on the project as there will be new updates; for example, we’re currently in the process of formalizing the INSERT, UPSERT, and REPLACE DML as part of RFC0011; we’ve also recently merged an experimental feature for the limited support on INSERT and DELETE DML statements.

Finally, I was wondering if you’d be interested in chatting with us about your use cases that could potentially use PartiQL? in that case, please feel free to create an issue with us on GitHub we can take it from there.

@mjg This is Ellen, technical program manager from Amazon PartiQL team. For reference, here is the partiql-spec GitHub repository where you can create an issue to the Amazon PartiQL team. If you are interested in talking through your use case and how PartiQL can help your organization in detail, feel free to share with me your email and I can set something up. Thank you.