Path extraction with #> and #>> in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the operator #> do in PostgreSQL JSON queries?

The #> operator extracts a JSON object or array at the specified path as JSON data, preserving its JSON type.

beginner

How does #>> differ from #> when extracting JSON data?

#>> extracts the JSON value at the specified path but returns it as text, not as JSON.

beginner

Given a JSON column data with value {"a": {"b": 10}}, what does data #> '{a,b}' return?

It returns the JSON value 10 as JSON type, not as text.

intermediate

What is the input format for the path argument in #> and #>> operators?

The path is given as a text array or a string representing an array of keys/indexes, for example '{key1,key2}'.

intermediate

Why would you use #>> instead of #> when extracting JSON data?

Use #>> when you want the extracted value as plain text for easier comparison or display, instead of JSON format.

What type of value does #> return when extracting JSON data?

A JSON

B Text

C Integer

D Boolean

#> returns the extracted value as JSON type, preserving its structure.

Which operator returns the extracted JSON value as text?

A #>>

B #>

C ->

D ->>

#>> returns the JSON value as text, unlike #> which returns JSON.

How do you specify the path for #> operator?

A As a boolean

B As a JSON object

C As a number

D As a text array or string array like '{key1,key2}'

The path is specified as a text array or string representing keys/indexes.

If you want to extract the value of key 'name' inside a JSON column 'info', which operator returns it as text?

A info #> '{name}'

B info #>> '{name}'

C info -> 'name'

D info ->> 'name'

#>> returns the value as text, which is useful for display or comparison.

What will data #> '{a,b}' return if data is {"a": {"b": 10}}?

A null

B 10 as text

C 10 as JSON

D An error

#> returns the value at path as JSON, so it returns 10 as JSON.

Explain how to extract a nested JSON value as JSON and as text using PostgreSQL operators.

Think about the difference between preserving JSON type and converting to text.

Describe a scenario where using #>> is more useful than #> for JSON path extraction.

Consider when you want to use the extracted value in a text context.