Pulling a value out: -> and ->>

The Commandant flips through the documents you’ve laid out in a staircase and sets them aside. He doesn’t have time to read every parcel from start to finish. The summary needs individual values: which products were viewed, at what price, and which cities the customers came from.

You open event 4. On February 11, customer 17 views “Plush cat toy” in app version 3.9.2. The price is in the document, "price": "1290", but it’s written in quotes. The next morning, customer 42 views the book “SQL for Pros” in app version 4.6.0, and in event 6 the same amount appears without quotes: "price": 1290.

The price is the same: 1,290 ₽. It’s just written two different ways. To PostgreSQL, those are two different things.

QUERY: You won’t have to take the whole parcel apart for one price. Find the right little box by its label and pull out just that one.

Here, that label is a key. PostgreSQL has two arrow operators for pulling out a value by key. They differ by just one character, but they return different types, and that changes how comparisons behave too.

The arrow ->: getting a value by key

Take event 1, a view of the headphones. You saw its payload in the last lesson: the keys app, user, price, product_id, schema_version.

The expression payload -> 'price' reads literally: take the document from payload, find the key price, and return the value under it. For event 1, that gives 5990. And payload -> 'user' returns the whole nested object {"id": 23, "city": "Санкт-Петербург"} (St. Petersburg). The size of the value doesn’t matter: the arrow pulls out whatever sits under the key you name.

After a single arrow, the result is still of type jsonb. Picture it as lifting a small box out of a big one without opening its lid yet. If there was a JSON string inside, it stays a JSON string, double quotes included.

The key is written as a string in single quotes, like ordinary text in SQL: 'price'. Keys are case-sensitive, so 'Price' and 'price' are different keys. A key that doesn’t exist gives NULL, with no error. We’ll work on cases like that separately in the third lesson.

In the cell below, the arrow pulls three values from three events, and the last column shows the type it returned. The result is printed the way PostgreSQL prints it: JSON strings keep their quotes. Before you run it, try to guess: what will the app column show for the three events, with quotes or without? And what will you see in price?

The double arrow ->>: getting text

The ->> operator also finds a value by key, but it returns the value as plain text, of type text. A JSON string loses its quotes: "4.6.0" becomes 4.6.0. The JSON number 5990 becomes plain text, four digits long. Even a nested object becomes text that looks like JSON, although to SQL it is now just a string of characters.

Back to the two parcels from the start of the lesson. In event 4, the price is stored as the JSON string "1290"; in event 6, as the JSON number 1290. Event 4 is already predictable. A single arrow returns a jsonb with a string inside, so the quotes stay in the output: "1290". The double arrow strips them, and you get the text 1290.

Now event 6. What will each arrow return? Compare the arrow and double_arrow columns for the two events before you run it.

Comparison: jsonb is compared with jsonb

The Commandant asks: how many views were there of products priced at 1,290 ₽? The catalog has two such products, the book “SQL for Pros” and “Plush cat toy”.

You’re tempted to write payload -> 'price' = '1290'. It runs without an error, which is exactly why it’s so easy to get wrong. On the left is a jsonb, and PostgreSQL parses the literal on the right as jsonb too. The text 1290 without quotes becomes a JSON number. Event 6, where the price is the number 1290, will match. Event 4, with the JSON string "1290", won’t: as you saw in the last lesson, a string and a number with the same digits are different things to jsonb.

With the double arrow, you’re comparing two text values. In both events the left side comes out as 1290 and the right side is 1290 as well, so both rows match.

Below are all the views of these two products with both checks. We’ve already worked through event 6. Before you run it, estimate the result for events 4, 18, and 35: they were sent by the old app, 3.9.2. What values will the two comparison columns show?

The chain: a box inside a box

In event 1, the customer’s city is not at the top level; it’s inside the user object. Here the arrows go one after another: payload -> 'user' ->> 'city'. The first pulls out the user object, the second takes city from it and returns the city as text.

Read an expression like this from left to right as a route: the document, the key user, the key city. Every intermediate arrow leaves its result as jsonb, so you can keep following the path. If you write payload ->> 'user' ->> 'city', the first double arrow turns the object into text, and PostgreSQL then answers operator does not exist: text ->> unknown. You can’t continue a JSON path through plain text.

Which arrow comes last depends on what you plan to do with the value you’ve found. For text in a report or a string comparison, you need the double one. If the path through the document isn’t finished yet, keep the single one.

Let’s look at three views. For event 1, the path to the city gives Санкт-Петербург (St. Petersburg), and payload -> 'user' ->> 'id' returns the customer ID, 23. Now events 6 and 8: what type will the buyer column get? In the document itself the customer ID is stored as a number, but the last arrow is a double one.

Now use the same technique on payment gateway responses. The payment status is at the top level, the customer’s email is inside customer, and the decline code is inside the error object. For payments 1, 2, and 9, the cell currently outputs only status. Add two more chains for the email and error_code columns, and before you run it, decide what error_code will contain for payment 1, which succeeded without an error.

Array element numbers

Array elements have no keys, but they do have numbers. Put a whole number to the right of a single arrow and it takes the element with that number: payload -> 'items' -> 0. Counting starts at zero, as in many programming languages: 0 is the first element, 1 the second.

Take order 5003, event 7: items holds three books. Let’s read payload -> 'items' -> 0 ->> 'name' step by step. The first arrow pulls out the array. The second takes element number 0, the object for the first item. The third pulls out the name as text. The result is SQL для профи ("SQL for Pros").

You can also count from the other end. A negative number -1 means the last element, -2 the one before it. That way you don’t need to know the array’s length in advance if you want the last item.

Going out of bounds doesn’t cause an error either. The arrow simply returns NULL, exactly as it does for a missing key.

Before you run it, fill in the four columns in your head. Which book is under number 2? What will number 3 return? And what name will -1 give?

Charge attempts in the gateway response are stored in the charges array. Usually there is one attempt, but payment 4 has two: the first ended in a timeout, and the second succeeded. Payment 11 has an empty array because the payment is still processing and there haven’t been any charge attempts yet.

The cell already outputs the status of the first attempt, first_try. Add last_try next to it, with the status of the last attempt. Before you run it, predict both columns for payments 3, 4, and 11.

From text to number

After the double arrow, the price is text. If you need to add it up, divide it, or compare it by magnitude, you have to turn the text into a number: (payload ->> 'price')::numeric.

The parentheses are mandatory here. The :: operator binds tighter than the arrow, so PostgreSQL reads payload ->> 'price'::numeric very differently from what you intend: it first tries to turn the word 'price' into a number and fails with invalid input syntax for type numeric: "price".

Go back to events 4 and 6. In the first, the price is stored as a JSON string, in the second as a JSON number. Before you run it, decide whether the values in the price column will be identical after the cast, and what type the last column will show.

Now money from the payment gateway. The amounts are stored in kopecks: for payment 1, it’s 797000. To get rubles, you cast the value to a number and divide by 100. round(…, 2) keeps two decimal places, because dividing a numeric without rounding gives a long tail of zeros.

Before you run it, work out how many rubles payments 1, 2, and 4 come to. The order number is next to each one, so you can check the amount against the order data you already know.

The error cannot cast jsonb string to type numeric

People often try to cut a corner here and cast the result of a single arrow to a number: (payload -> 'price')::numeric. This kind of jsonb cast has been supported since PostgreSQL 11. On event 6, it really does work, because the key holds a JSON number.

Event 4 holds a JSON string there. A jsonb can be cast directly to a number only when a JSON number is already inside. On a string, PostgreSQL stops:

ERROR:  cannot cast jsonb string to type numeric

The error text describes the problem well: what’s inside is a string. PostgreSQL doesn’t check whether its characters look like a number. The double arrow takes a different route: first you get the plain text 1290, and text like that casts to a number without any trouble.

Test this with a query. The Commandant needs the viewing history of “Plush cat toy”, product 18: the date and the price as a number. The query below fails on the very first matching row. Fix it with a single replacement.

Sorting: why 9 is bigger than 800

The Commandant’s next request is about household appliances. The cards of two appliances list their power: a coffee maker at 800 W and a desk lamp at 9 W. They need to be ordered from the more powerful to the less powerful.

The power is stored in attrs under the key power_w. The double arrow returns it as text, and PostgreSQL compares text character by character, left to right. For '800' and '9', 8 and 9 get compared first. The nine is bigger, and that’s the end of it: the remaining two characters in 800 don’t take part at all. That’s how the lamp suddenly ends up above the coffee maker.

With numbers of different lengths, this mistake is easy to spot: as text, '10' is less than '9', and '1000' is less than '12'. If all the values have the same length, say 1290 and 5990, the query looks perfectly fine for a while. I wouldn’t let a sort like that into a report without testing it on a short number and a long one.

Which appliance comes out first if you sort the text attrs ->> 'power_w' in descending order? Answer first, then run it.

Before sorting, let’s turn the text into a number: ORDER BY (attrs ->> 'power_w')::numeric DESC. Now PostgreSQL compares 800 with 9 as numbers, and the coffee maker comes out first.

The same mistake shows up in WHERE. The condition attrs ->> 'power_w' > '100' compares two strings. For the lamp it’s true: in the first character, '9' is already greater than the '1' of '100'.

Below are three “more powerful than 100 W” checks for the same appliances. The single-arrow variant, column as_jsonb, can be worked out in advance. attrs -> 'power_w' > '100' compares a jsonb with a jsonb; both values in the documents are JSON numbers, so they’re compared by magnitude. The coffee maker gives true, the lamp false. Try filling in the other two columns yourself.

Now with no ready-made solution. The piece count is recorded for three products: the dinnerware set has 12 pieces, the building set 120, the puzzle 1000. First, arrange these three values in your head the way a text sort in ascending order would. Run the cell and check yourself. Then fix the ORDER BY right in the editor so the products go by the actual number of pieces, and run the query again.

The lesson’s final task comes with no hint in the main text. The Commandant is putting together a briefing on toys: the minimum age for giving each one as a gift, from youngest to oldest. The query runs and even starts the list correctly, with the plush toy for the youngest children. But then the order breaks down: 0, 12, 3, 8. Somehow, the puzzle for twelve-year-olds ended up second.

Common mistakes

Comparing a single arrow with a number

Row: views of two products priced at 1,290 ₽.

Expectation: payload -> 'price' = '1290' will find all views at that price.

In practice, the query finds only events where the price is stored as a number. The JSON string "1290" and the JSON number 1290 are different to PostgreSQL, so three of the four views quietly disappear from the result.

How to check: if you’re comparing with text, use ->>; for calculations, cast the result to a number. If the type isn’t obvious, output jsonb_typeof(payload -> 'price') alongside it.

Putting a double arrow in the middle of a chain

Row: event 1, payload ->> 'user' ->> 'city'.

Expectation: the customer’s city.

In practice, you get the error operator does not exist: text ->> unknown. The first double arrow has already turned the user object into text, and there’s nowhere for a JSON path to continue from text.

How to check: while the path continues, use single arrows; put the double arrow only on the final step.

Forgetting parentheses around an arrow

Row: event 6.

Expectation: payload ->> 'price'::numeric will return the price as a number, and payload -> 'price' + 1 will add one ruble to it.

In practice, both expressions fail. :: and + bind more tightly than the arrows, so PostgreSQL starts with something other than the extraction result. The errors are different, invalid input syntax for type numeric: "price" and invalid input syntax for type integer: "price", but the cause is the same: the database is trying to process the word 'price' itself.

How to check: if you need another operation after an arrow, first wrap the extraction result in parentheses: (payload ->> 'price')::numeric + 1.

Putting the element number in quotes

Row: order 5003, payload -> 'items' -> '0'.

Expectation: the first item in the order.

In practice, you get NULL. '0' in quotes is text, so PostgreSQL looks for an object key with that name. An array has no such key, and the database returns an empty value without an error.

How to check: write the element number as an integer without quotes: -> 0.

Sorting numbers as text

Row: an 800 W coffee maker and a 9 W lamp.

Expectation: ORDER BY attrs ->> 'power_w' DESC will put the coffee maker first.

In practice, the lamp comes first: in a text comparison, the first character 9 is greater than 8.

How to check: add numbers of different lengths to your test sample. With four-digit prices, the problem can easily stay hidden, while a product priced at 990 ₽ reveals it immediately.

Applying it: pulling values out of your own documents

Before you reach for an arrow, it helps to answer two questions. What will I do with the value once I’ve found it? How is it actually stored in real JSON?

For text in a report or a comparison with a string, you usually need ->>. While you’re still following a path through the document, or you really do want to compare JSON values, stay with ->. For calculations, numeric sorting, and comparisons with numbers, first pull out the text and cast it: (… ->> 'key')::numeric. For dates, the same trick works with ::date or ::timestamp.

You can check the actual type with jsonb_typeof. I’d do that at least once on real data, even if the API documentation promises a number. In our parcels, the old app sent the price as the string "1290" and the new one as the number 1290; a schema alone wouldn’t have been enough to draw that conclusion.

For a quick check, keep two awkward examples handy: 9 next to 800, and "1290" next to 1290. The first pair catches text sorting of numbers with different lengths; the second catches a mix of a JSON string and a JSON number. With these, the mistake is usually obvious right away.