We've talked a lot about building up JSON objects, whether that's JSON, JSONB, JSON Object, JSON Array, all sorts of functions to build them up. Now, let's imagine we've got some JSON columns stored. How do we get individual pieces of data out of JSON blobs? Let's look at JSON extract first, and then we're gonna look at a few other convenient methods. So to do JSON extract, you have JSON and then the path. So what we're gonna do here is we're gonna pass in JSON of A as foo, we'll do B as you guessed it, bar, and we'll do, why don't we just do C as three?

And now for the path, remember that the path has to start with the dollar sign. And if you do a semicolon, you'll see, great, that works already. And we can start working up the path and say, let's go ahead and just pull out A. This is a difference from MySQL. This JSON extract right here, this behavior exists in MySQL, but it is fundamentally different. And so you gotta look out for it, keep that in mind. I'm gonna show you why it's different here in a second. I wanna show you one more thing before we do that. If you were to pass a set of, you can just keep going. You can just do variadic arguments here. It will create an array, which is very, very cool. And so you can say, yeah, give me an array full of A and C. And it says, yes, A is a string surrounded by quotes, and C is an integer.

Now this is the difference between MySQL and SQLite in this regard. You'll notice that both of these values are JSON values. This value here is a JSON value. What I mean by that is it's valid JSON. It is a quoted string. This is a SQL value. This is a SQL text value. In MySQL, the JSON extract function would have returned a quoted foo here instead of just a bare foo. Because in MySQL, JSON extract always returns valid JSON, not the case in SQLite. In SQLite, if it's a single key that's being extracted and that's a string or a number or a valid SQLite data type, it comes back as a SQLite data type, not as a JSON data type. Maybe interesting, maybe not, I don't know. But let me show you a little bit more. Let's look at a way that we can prove that. So if we were to do select JSON and then we were to just drop our made up JSON object in here, and then we're gonna use this single arrow operator, which I'll explain in a second. But if we do dollar.A and we extract just that A key, that is what MySQL would've done with JSON extract. So this operator right here guarantees that you're gonna get JSON back, not a native data type. And so if we go back and we compare that to, we compare that to this guy, you'll see it's very different. It looks the same, but boy is that different.

So the thing to remember is JSON extract will convert it to a native data type, a native SQL data type, where this single arrow will leave it as valid JSON. You have a third option, and that is this, what would in MySQL be called the unquoting extraction operator. I suppose that's probably valid here too, because what happens is it unquotes it. And so this JSON extract, it's kind of a mix of both of these because it can return JSON or it can return native data types depending on what you're pulling out. Because remember, we looked at JSON extract with two arguments and it returned, that's JSON, that is not a valid SQL data type. That is JSON. But when you use it with just a single argument, it returns a native. So it kind of spans both worlds and can be very useful. I have no problem with the JSON extract function, but if you need to force it, you can use the double arrow or you can use the single arrow depending on which one you're after.

Now, to maintain a little bit of compatibility with Postgres, what happens here is for this arrow operator, you can just reference a key directly. We removed that rule that I've been telling you about about the dollar sign. It's just gone. That is, and you can do it with the double arrow or the unquoting extraction operator as well. That is to maintain some semblance of compatibility with Postgres because I believe they allow that. Now, if we were to try that with JSON extract, that is not going to work because that is explicitly a path argument. And so what I've been telling you still holds true, thank goodness. If it is a path argument, it must begin with a dollar sign. There is a special exception for this operator because this is not a path argument. This is just an operator, and then it accepts a key or a path. And so you can do it like that. If we had, let's just do it, let's just have some fun here. If we had an array, so if we had 1, 2, 3, 4 as JSON, and then you did this, you can just say, ah, always do that. You can just hit the key directly and so that will work. And you'll notice those two things are not different. The single arrow and the double arrow in this case are not different because the JSON representation of the number two is a bare two, and the SQL representation of a number two is a bare two. And so that only really applies for strings, whether they end up quoted or not quoted.

Okay, now you know how to get stuff out of a JSON object, whether that object is constructed on the fly like we've done here, or stored as a column, probably, stored as a column in your database. You can extract things, you can pull things out, and depending on what you need, you can use JSON extract. You can use the unquoting extraction operator or just the regular extraction operator.