We're going to move on from JSON extraction into JSON containment. Now we're going to test JSON blobs to see if they contain other JSON blobs. I have some more hardcoded examples. I do have a table that we're going to use in this one. First I want to look at these hardcoded examples and we can use this containment operator to see if the object on the left contains the object on the right.

Let's go ahead and do something we know will come back true. Does the object a:1 b:2 contain a:1? You better believe that it does. Does it contain a:2? In fact, it does not. You can switch this around and go the other direction and you'll get false there, because this object a:1, does not contain a:1 b:2. However, if you were to make them equal, you would see that that does work. We're going to flip that back around and we're going to operate this way. You can do more than one. You can do a:1 b:2. If this one had a c:3, you'd see that that is still true because it is a partial match. This object contains this object. It's not comparing if they're equal, it's just comparing if that subset is present in the preceding object. You can do this with arrays as well.

Here I have a hard coated array of apple, banana, cherry, and you can do a comparison here to see if this array contains apple, as jsonb, and you'll see that it does. Does it contain a misspelling of apple? In fact, it does not. Same deal goes here. You can say, does it contain apple and cherry? Even though they're not in that order, it doesn't matter. We're just testing to see if the first array contains all of the items from the second array.

Moving on, you can of course do subsets as well. You can do partial subsets maybe. We'll say, does this object on the left contain customer, but also the customer must have a name of Alice? Does it contain that? Yes, in fact it does. We're doing this partial match of a deeply nested subset, which is kind of cool. Then finally we have a table here. Let me do select * from orders JSON, and instead of making proper columns, I shoved it all into a JSON column, which I've told you not to do, but it does make for an interesting example. Here we have all of this data here and we're going to check the status right here. There are a few ways we can do this.

I'm going to show you the JSON containment way first, where details contains, and then we can do a status of shipped. If we do that, we get one, three, and five back because those are the ones where the details JSON object contains this smaller object, which is just status and shipped. We can do this with extraction and which one you choose is kind of up to you. I'm going to show you extraction as well. Here's the containment method, but we're going to get rid of that and instead we're going to use the extraction method and we're going to say, unquote, the not shipped, unquote the status key and test is it equal to shipped, and we still get one, three, and five back.

For a simple key extraction in comparison, this is probably the method that I would use if I were testing something a little bit more complicated rather than right extracting a bunch and comparing them discreetly. I probably would use this JSON overlapping operator. At the end of the day, it's a little bit of a preference. It's kind of up to you which one you choose. When it comes to indexing part of a JSON blob, we are going to use this extraction operator. Again, if you're looking for some complex overlapping comparison, I would use this containment operator instead.