Join us on
Star us on
Get Started
Slack
GitHub
Get Started
v2.5 (latest) v2.2 (stable) v2.1 (earlier version) v2.0 (earlier version) v1.3 (earlier version)
  • YUGABYTEDB CORE
    • Quick start
      • 1. Install YugabyteDB
      • 2. Create a local cluster
      • 3. Explore distributed SQL
      • 4. Build an application
        • Java
        • NodeJS
        • Go
        • Python
        • Ruby
        • C#
        • PHP
        • C++
        • C
        • Scala
    • Explore features
      • YSQL vs PostgreSQL
        • Schemas and Tables
        • Data Types
      • Fault tolerance
      • Horizontal Scalability
        • Scaling Transactions
        • Sharding Data
      • Transactions
        • Distributed Transactions
        • Isolation Levels
        • Explicit Locking
      • JSON Support
      • Multi-Region Deployments
        • Sync replication (3+ regions)
        • Async Replication (2+ regions)
        • Row-Level Geo-Partitioning
      • Follower reads
      • Colocated tables
      • Change data capture (CDC)
      • Observability
        • Prometheus Integration
    • Develop
      • Learn app development
        • 1. SQL vs NoSQL
        • 2. Data modeling
        • 3. Data types
        • 4. ACID transactions
        • 5. Aggregations
        • 6. Batch operations
        • 7. Date and time
        • 8. Strings and text
        • 9. TTL for data expiration
      • Ecosystem integrations
        • Apache Kafka
        • Apache Spark
        • JanusGraph
        • KairosDB
        • Presto
        • Metabase
      • Build GraphQL apps
        • Hasura
        • Prisma
      • Real-world examples
        • E-Commerce app
        • IoT fleet management
        • Retail Analytics
      • Explore sample apps
      • Best practices
    • Migrate
      • Migration process overview
      • Migrate from PostgreSQL
        • Convert a PostgreSQL schema
        • Migrate a PostgreSQL application
        • Export PostgreSQL data
        • Prepare a cluster
        • Import PostgreSQL data
        • Verify Migration
    • Deploy
      • Deployment checklist
      • Manual deployment
        • 1. System configuration
        • 2. Install software
        • 3. Start YB-Masters
        • 4. Start YB-TServers
        • 5. Verify deployment
      • Kubernetes
        • Single-zone
          • Open Source
          • Amazon EKS
          • Google Kubernetes Engine
          • Azure Kubernetes Service
        • Multi-zone
          • Amazon EKS
          • Google Kubernetes Engine
        • Multi-cluster
          • Google Kubernetes Engine
        • Best practices
        • Connect clients
      • Docker
      • Public clouds
        • Amazon Web Services
        • Google Cloud Platform
        • Microsoft Azure
      • Multi-DC deployments
        • Three+ data center (3DC)
        • Two data center (2DC)
        • Read replica clusters
      • Change data capture (CDC)
        • CDC to Kafka
    • Benchmark
      • TPC-C
      • sysbench
      • YCSB
      • Key-value workload
      • Large datasets
      • Scalability
        • Scaling queries
      • Resilience
        • Jepsen testing
    • Secure
      • Security checklist
      • Enable Authentication
        • Enable User Authentication
        • Configure ysql_hba_conf_csv
      • Authentication Methods
        • Password Authentication
        • LDAP Authentication
        • Host-Based Authentication
        • Trust Authentication
      • Role-Based Access Control
        • Overview
        • Manage Users and Roles
        • Grant Privileges
        • Row-Level Security (RLS)
        • Column-Level Security
      • Encryption in transit
        • Create client certificates
        • Create server certificates
        • Enable server-to-server encryption
        • Enable client-to-server encryption
        • Connect to clusters
      • Encryption at rest
      • Column-Level Encryption
      • Audit Logging
        • Configure Audit Logging
        • Session-Level Audit Logging
        • Object-Level Audit Logging
    • Manage
      • Back up and restore
        • Back up data
        • Restore data
        • Snapshot and restore data
      • Migrate data
        • Bulk import
        • Bulk export
      • Change cluster configuration
      • Diagnostics reporting
      • Upgrade a deployment
      • Grow cluster
    • Troubleshoot
      • Troubleshooting
      • Common error messages
      • Cluster level issues
        • YCQL connection issues
        • YEDIS connection Issues
        • Recover tserver/master
        • Replace a failed YB-TServer
        • Replace a failed YB-Master
      • Node level issues
        • Check servers
        • Inspect logs
        • System statistics
        • Disk failure
    • Contribute
      • Core database
        • Contribution checklist
        • Build the source
        • Configure a CLion project
        • Run the tests
  • YUGABYTE PLATFORM
    • Yugabyte Platform
      • Overview
        • Install
        • Configure
      • Install Yugabyte Platform
        • Prerequisites
        • Prepare the environment
        • Install software
        • Prepare nodes (on-prem)
        • Uninstall software
      • Configure Yugabyte Platform
        • Create admin user
        • Configure the cloud provider
        • Configure the backup target
        • Configure alerts and health checking
        • Create and edit instance tags
      • Create deployments
        • Multi-zone universe
        • Multi-region universe
        • Read replica cluster
      • Manage deployments
        • Start and stop processes
        • Add a node
        • Remove a node
        • Edit a universe
        • Edit configuration flags
        • Upgrade the YugabyteDB software
        • Delete a universe
        • Migrate to Helm 3
      • Back up and restore universes
        • Configure backup storage
        • Back up universe data
        • Restore universe data
        • Schedule data backups
      • Security
        • Security checklist
        • Customize ports
        • Authorization platform
        • Create a KMS configuration
        • Enable encryption at rest
        • Enable encryption in transit (TLS)
        • Network security
      • Alerts and monitoring
        • Live Queries dashboard
      • Troubleshoot
        • Install and upgrade issues
        • Universe issues
      • Administer Yugabyte Platform
        • Back up and restore Yugabyte Platform
  • YUGABYTE CLOUD
    • Yugabyte Cloud
      • Free tier
      • Create clusters
      • Monitor clusters
      • Create databases
      • Manage database access
      • Connect to clusters
  • REFERENCE
    • Reference
    • Architecture
      • Design goals
      • Key concepts
        • Universe
        • YB-TServer Service
        • YB-Master Service
      • Core functions
        • Universe creation
        • Table creation
        • Write IO path
        • Read IO path
        • High availability
      • Layered architecture
      • Query layer
        • Overview
      • DocDB transactions layer
        • Transactions overview
        • Transaction isolation levels
        • Explicit locking
        • Single-row transactions
        • Distributed transactions
        • Transactional IO path
      • DocDB sharding layer
        • Hash & range sharding
        • Tablet splitting
        • Colocated tables
      • DocDB replication layer
        • Replication
        • xCluster replication
        • Read replicas
        • Change data capture (CDC)
      • DocDB storage layer
        • Persistence
        • Performance
    • APIs
      • YSQL
        • The SQL language
          • SQL statements
            • ABORT
            • ALTER DATABASE
            • ALTER DEFAULT PRIVILEGES
            • ALTER DOMAIN
            • ALTER GROUP
            • ALTER POLICY
            • ALTER ROLE
            • ALTER SEQUENCE
            • ALTER TABLE
            • ALTER USER
            • BEGIN
            • CALL
            • COMMENT
            • COMMIT
            • COPY
            • CREATE AGGREGATE
            • CREATE CAST
            • CREATE DATABASE
            • CREATE DOMAIN
            • CREATE EXTENSION
            • CREATE FUNCTION
            • CREATE GROUP
            • CREATE INDEX
            • CREATE OPERATOR
            • CREATE OPERATOR CLASS
            • CREATE POLICY
            • CREATE PROCEDURE
            • CREATE ROLE
            • CREATE RULE
            • CREATE SCHEMA
            • CREATE SEQUENCE
            • CREATE TABLE
            • CREATE TABLE AS
            • CREATE TRIGGER
            • CREATE TYPE
            • CREATE USER
            • CREATE VIEW
            • DEALLOCATE
            • DELETE
            • DO
            • DROP AGGREGATE
            • DROP CAST
            • DROP DATABASE
            • DROP DOMAIN
            • DROP EXTENSION
            • DROP FUNCTION
            • DROP GROUP
            • DROP OPERATOR
            • DROP OPERATOR CLASS
            • DROP OWNED
            • DROP POLICY
            • DROP PROCEDURE
            • DROP ROLE
            • DROP RULE
            • DROP SEQUENCE
            • DROP TABLE
            • DROP TRIGGER
            • DROP TYPE
            • DROP USER
            • END
            • EXECUTE
            • EXPLAIN
            • GRANT
            • INSERT
            • LOCK
            • PREPARE
            • REASSIGN OWNED
            • RESET
            • REVOKE
            • ROLLBACK
            • SELECT
            • SET
            • SET CONSTRAINTS
            • SET ROLE
            • SET SESSION AUTHORIZATION
            • SET TRANSACTION
            • SHOW
            • SHOW TRANSACTION
            • TRUNCATE
            • UPDATE
            • VALUES
          • WITH clause
            • WITH clause—SQL syntax and semantics
            • recursive CTE
            • case study—traversing an employee hierarchy
            • traversing general graphs
              • graph representation
              • common code
              • undirected cyclic graph
              • directed cyclic graph
              • directed acyclic graph
              • rooted tree
              • Unique containing paths
              • Stress testing find_paths()
            • case study—Bacon Numbers from IMDb
              • Bacon numbers for synthetic data
              • Bacon numbers for IMDb data
        • Data types
          • Array
            • array[] constructor
            • Literals
              • Text typecasting and literals
              • Array of primitive values
              • Row
              • Array of rows
            • FOREACH loop (PL/pgSQL)
            • array of DOMAINs
            • Functions and operators
              • ANY and ALL
              • Array comparison
              • Array slice operator
              • Array concatenation
              • Array properties
              • array_agg(), unnest(), generate_subscripts()
              • array_fill()
              • array_position(), array_positions()
              • array_remove()
              • array_replace() / set value
              • array_to_string()
              • string_to_array()
          • Binary
          • Boolean
          • Character
          • Date and time
          • JSON
            • JSON literals
            • Primitive and compound data types
            • Code example conventions
            • Indexes and check constraints
            • Functions & operators
              • ::jsonb, ::json, ::text (typecast)
              • ->, ->>, #>, #>> (JSON subvalues)
              • - and #- (remove)
              • || (concatenation)
              • = (equality)
              • @> and <@ (containment)
              • ? and ?| and ?& (key or value existence)
              • array_to_json()
              • jsonb_agg()
              • jsonb_array_elements()
              • jsonb_array_elements_text()
              • jsonb_array_length()
              • jsonb_build_object()
              • jsonb_build_array()
              • jsonb_each()
              • jsonb_each_text()
              • jsonb_extract_path()
              • jsonb_extract_path_text() and json_extract_path_text()
              • jsonb_object()
              • jsonb_object_agg()
              • jsonb_object_keys()
              • jsonb_populate_record()
              • jsonb_populate_recordset()
              • jsonb_pretty()
              • jsonb_set() and jsonb_insert()
              • jsonb_strip_nulls()
              • jsonb_to_record()
              • jsonb_to_recordset()
              • jsonb_typeof()
              • row_to_json()
              • to_jsonb()
          • Money
          • Numeric
          • Serial
          • UUID
        • Functions and operators
          • Aggregate functions
            • Informal functionality overview
            • Invocation syntax and semantics
            • grouping sets, rollup, cube
            • Per function signature and purpose
              • avg(), count(), max(), min(), sum()
              • array_agg(), string_agg(), jsonb_agg(), jsonb_object_agg()
              • bit_and(), bit_or(), bool_and(), bool_or()
              • variance(), var_pop(), var_samp(), stddev(), stddev_pop(), stddev_samp()
              • linear regression
                • covar_pop(), covar_samp(), corr()
                • regr_%()
              • mode(), percentile_disc(), percentile_cont()
              • rank(), dense_rank(), percent_rank(), cume_dist()
            • case study—percentile_cont() and the "68–95–99.7" rule
            • case study—linear regression on COVID data
              • Download the COVIDcast data
              • Ingest the COVIDcast data
                • Inspect the COVIDcast data
                • Copy the .csv files to staging tables
                • Check staged data conforms to the rules
                • Join the staged data into a single table
                • SQL scripts
                  • Create cr_staging_tables()
                  • Create cr_copy_from_scripts()
                  • Create assert_assumptions_ok()
                  • Create xform_to_covidcast_fb_survey_results()
                  • ingest-the-data.sql
              • Analyze the COVIDcast data
                • symptoms vs mask-wearing by day
                • Data for scatter-plot for 21-Oct-2020
                • Scatter-plot for 21-Oct-2020
                • SQL scripts
                  • analysis-queries.sql
                  • synthetic-data.sql
          • currval()
          • lastval()
          • nextval()
          • Window functions
            • Informal functionality overview
            • Invocation syntax and semantics
            • Per function signature and purpose
              • row_number(), rank() and dense_rank()
              • percent_rank(), cume_dist() and ntile()
              • first_value(), nth_value(), last_value()
              • lag(), lead()
              • Tables for the code examples
                • table t1
                • table t2
                • table t3
                • table t4
            • case study—analyzing a normal distribution
              • Bucket allocation scheme
              • do_clean_start.sql
              • cr_show_t4.sql
              • cr_dp_views.sql
              • cr_int_views.sql
              • cr_pr_cd_equality_report.sql
              • cr_bucket_using_width_bucket.sql
              • cr_bucket_dedicated_code.sql
              • do_assert_bucket_ok
              • cr_histogram.sql
              • cr_do_ntile.sql
              • cr_do_percent_rank.sql
              • cr_do_cume_dist.sql
              • do_populate_results.sql
              • do_report_results.sql
              • do_compare_dp_results.sql
              • do_demo.sql
              • Reports
                • Histogram report
                • dp-results
                • compare-dp-results
                • int-results
        • Extensions
        • Keywords
        • Reserved names
      • YCQL
        • ALTER KEYSPACE
        • ALTER ROLE
        • ALTER TABLE
        • CREATE INDEX
        • CREATE KEYSPACE
        • CREATE ROLE
        • CREATE TABLE
        • CREATE TYPE
        • DROP INDEX
        • DROP KEYSPACE
        • DROP ROLE
        • DROP TABLE
        • DROP TYPE
        • GRANT PERMISSION
        • GRANT ROLE
        • REVOKE PERMISSION
        • REVOKE ROLE
        • USE
        • INSERT
        • SELECT
        • EXPLAIN
        • UPDATE
        • DELETE
        • TRANSACTION
        • TRUNCATE
        • Simple expressions
        • Subscripted expressions
        • Function call
        • Operators
        • BLOB
        • BOOLEAN
        • Collection
        • FROZEN
        • INET
        • Integer and counter
        • Non-integer
        • TEXT
        • DATE, TIME, and TIMESTAMP
        • UUID and TIMEUUID
        • JSONB
        • Date and time
        • BATCH
    • CLIs
      • yb-ctl
      • yb-docker-ctl
      • ysqlsh
      • ycqlsh
      • yb-admin
      • yb-ts-cli
      • ysql_dump
      • ysql_dumpall
    • Configuration
      • yb-tserver
      • yb-master
      • yugabyted
      • Default ports
    • Drivers
      • Client drivers for YSQL API
      • YugabyteDB JDBC Driver
      • Client drivers for YCQL
      • Spring Data YugabyteDB
    • Connectors
      • Kafka Connect YugabyteDB
    • Third party tools
      • DBeaver
      • DbSchema
      • pgAdmin
      • SQL Workbench/J
      • TablePlus
      • Visual Studio Code
    • Sample datasets
      • Chinook
      • Northwind
      • PgExercises
      • SportsDB
  • RELEASES
    • Releases
    • Releases overview
    • Release versioning
    • What's new
      • v2.5 (latest)
      • v2.4.0 (stable)
    • Earlier releases
      • v2.3.3
      • v2.3.2
      • v2.3.1
      • v2.3.0
      • v2.1.8
      • v2.1.6
      • v2.2.0
      • v2.1.5
      • v2.1.4
      • v2.1.3
      • v2.1.2
      • v2.1.1
      • v2.1.0
      • v2.0.11
      • v2.0.10
      • v2.0.9
      • v2.0.8
      • v2.0.7
      • v2.0.6
      • v2.0.5
      • v2.0.3
      • v2.0.1
      • v2.0.0
      • v1.3.1
      • v1.3.0
      • v1.2.12
      • v1.2.11
      • v1.2.10
      • v1.2.9
      • v1.2.8
      • v1.2.6
      • v1.2.5
      • v1.2.4
  • FAQ
    • Comparisons
      • Amazon Aurora
      • Google Cloud Spanner
      • CockroachDB
      • TiDB
      • Vitess
      • MongoDB
      • FoundationDB
      • Amazon DynamoDB
      • Azure Cosmos DB
      • Apache Cassandra
      • PostgreSQL
      • Redis in-memory store
      • Apache HBase
    • FAQs
      • General FAQ
      • Operations FAQ
      • API compatibility FAQ
      • Yugabyte Platform FAQ
  • MISC
    • YEDIS
      • Quick start
      • Develop
        • Build an application
        • C#
        • C++
        • Go
        • Java
        • NodeJS
        • Python
      • API reference
        • APPEND
        • AUTH
        • CONFIG
        • CREATEDB
        • DELETEDB
        • LISTDB
        • SELECT
        • DEL
        • ECHO
        • EXISTS
        • EXPIRE
        • EXPIREAT
        • FLUSHALL
        • FLUSHDB
        • GET
        • GETRANGE
        • GETSET
        • HDEL
        • HEXISTS
        • HGET
        • HGETALL
        • HINCRBY
        • HKEYS
        • HLEN
        • HMGET
        • HMSET
        • HSET
        • HSTRLEN
        • HVALS
        • INCR
        • INCRBY
        • KEYS
        • MONITOR
        • PEXPIRE
        • PEXPIREAT
        • PTTL
        • ROLE
        • SADD
        • SCARD
        • RENAME
        • SET
        • SETEX
        • PSETEX
        • SETRANGE
        • SISMEMBER
        • SMEMBERS
        • SREM
        • STRLEN
        • ZRANGE
        • TSADD
        • TSCARD
        • TSGET
        • TSLASTN
        • TSRANGEBYTIME
        • TSREM
        • TSREVRANGEBYTIME
        • TTL
        • ZADD
        • ZCARD
        • ZRANGEBYSCORE
        • ZREM
        • ZREVRANGE
        • ZSCORE
        • PUBSUB
        • PUBLISH
        • SUBSCRIBE
        • UNSUBSCRIBE
        • PSUBSCRIBE
        • PUNSUBSCRIBE
    • Legal
      • Third party software
> APIs > YSQL > Data types > JSON > Functions & operators >

-> and ->> and #> and #>> (JSON subvalue operators)

  • The   ->   operator
  • The   #>   operator
  • The   ->>   and   #>>   operators
  • Summary:   ->   versus   ->>   and   #>   versus   #>>

Purpose: Read a JSON value at a specified path. The > variants return a json or jsonb value, according to the data type of the input. And the >> variants return a text value. The #> and #>> variants differ from -> and ->> variants in how the path is specified.

The   ->   operator

Purpose: Read the value specified by a one-step path returning it as a json or jsonb value.

Signature For the jsonb overload:

input values:       jsonb -> [int | text] [ -> [int | text] ]*
return value:       jsonb

Notes: The -> operator requires that the JSON value is an object or an array. Key is a SQL value. When key is a SQL text value, it reads the JSON value of the key-value pair with that key from an object. When key is a SQL integer value, it reads the JSON value at that index key from an array. If the input JSON value is json, then the output JSON value is json, and correspondingly if the input JSON value is jsonb.

Reading a key value:

do $body$
declare
  j  constant jsonb := '{"a": 1, "b": {"x": 1, "y": 19}, "c": true}';
  jsub constant jsonb := j  -> 'b';
  expected_jsub constant jsonb := '{"x": 1, "y": 19}';
begin
  assert
    jsub = expected_jsub,
  'unexpected';
end;
$body$;

Reading an array value. (The first value in an array has the index 0.)

do $body$
declare
  j  constant jsonb := '["a", "b", "c", "d"]';
  j_first constant jsonb := j -> 0;
  expected_first constant jsonb := '"a"';
begin
  assert
    j_first = expected_first,
  'unexpected';
end;
$body$;

The   #>   operator

Purpose: Read the value specified by a multi-step path returning it as a json or jsonb value.

Signature for the jsonb overload:

input value:        jsonb #> text[]
return value:       jsonb

Notes: An arbitrarily deeply located JSON subvalue is identified by its path from the topmost JSON value. In general, a path is specified by a mixture of keys for object subvalues and index values for array subvalues.

Consider this JSON value:

[
  1,
  {
    "x": [
      1,
      true,
      {"a": "cat", "b": "dog"},
      3.14159
    ],
    "y": true
  },
  42
]
  • At the topmost level of decomposition, it's an array of three subvalues.

  • At the second level of decomposition, the second array subvalue (i.e. the value with the index of 1) is an object with two key-value pairs called "x" and "y".

  • At the third level of decomposition, the subvalue for the key "x" is an array of subvalues.

  • At the fourth level of decomposition, the third array subvalue (i.e. the value with the index of 2) is an object with two key-value pairs called "a" and "b".

  • And at the fifth level of decomposition, the subvalue for key "b" is the primitive string value "dog".

This, therefore, is the path to the primitive JSON string value "dog":

-> 1 -> 'x' -> 2 -> 'b'

(Recall that array value indexing starts at zero.)

The #> operator is a convenient syntax sugar shorthand for specifying a long path compactly, thus:

#> array['1', 'x', '2', 'b']::text[]

Notice that with the -> operator, integers must be presented as such (so that '1' rather than 1 would silently read out NULL. However, with the #> operator, integers must be presented as convertible text values because all the values in a SQL array must have the same data type.

The PL/pgSQL ASSERT confirms that both the -> path specification and the #> path specification produce the same result, thus:

do $body$
declare
  j constant jsonb := '
    [
      1,
      {
        "x": [
          1,
          true,
          {"a": "cat", "b": "dog"},
          3.14159
        ],
        "y": true
      },
      42
    ]';

  one constant int := 1;
  two constant int := 2;
  x constant text := 'x';
  b constant text := 'b';
  one_t constant text := '1';
  two_t constant text := '2';

  jsub_1 constant jsonb := j -> one -> x -> two -> b;
  jsub_2 constant jsonb := j #> array[one_t, x, two_t, b];

  expected_jsub constant jsonb := '"dog"';
begin
  assert
    (jsub_1 = expected_jsub) and
    (jsub_2 = expected_jsub),
  'unexpected';
end;
$body$;

The paths are written using PL/pgSQL variables so that, as a pedagogic device, the data types are explicit.

The   ->>   and   #>>   operators

Purpose: Read the specified JSON value as a text value.

Signatures For the jsonb overloads:

input values:       jsonb ->> [int | text] [ -> [int | text] ]*
return value:       text

and:

input value:        jsonb #>> text[]
return value:       text

Notes: The -> operator returns a JSON object. When the targeted value is compound, the ->> operator returns the ::text typecast of the value. But when the targeted value is primitive, the ->> operator returns the value itself, typecast to a text value. In particular; a JSON number value is returned as the ::text typecast of that value (for example '4.2'), allowing it to be trivially ::numeric typecasted back to what it actually is; a JSON boolean value is returned as the ::text typecast of that value ('TRUE' or 'FALSE'), allowing it to be trivially ::boolean typecasted back to what it actually is; a JSON string value is return as is as a text value; and a JSON null value is returned as a genuine SQL NULL so that the IS NULL test is TRUE.

The difference in semantics between the -> operator and the ->> operator is vividly illustrated (as promised above) by targeting this primitive JSON string subvalue:

"\"First line\"\n\"second line\""

from the JSON value in which it is embedded. For example, here it is the value of the key "a" in a JSON object:

do $body$
declare
  j constant jsonb := '{"a": "\"First line\"\n\"second line\""}'::jsonb;

  a_value_j constant jsonb := j ->  'a';
  a_value_t constant text  := j ->> 'a';

  expected_a_value_j constant jsonb :=
    '"\"First line\"\n\"second line\""'::jsonb;
  
  expected_a_value_t constant text := '"First line"
"second line"';

begin
  assert
    (a_value_j = expected_a_value_j) and
    (a_value_t = expected_a_value_t),
  'unexpected';
end;
$body$;

Understanding the difference between the -> operator and the ->> operator completely informs the understanding of the difference between the #> operator and the #>> operator.

The >> variant (both for -> vs ->> and for #> vs #>>) is interesting mainly when the denoted subvalue is a primitive value (as the example above showed for a primitive string value). If you read such a subvalue, it's most likely that you'll want to cast it to a value of the appropriate SQL data type, numeric, text, or boolean. You might use your understanding of a value's purpose (for example "quantity ordered" or "product SKU") to cast it to, say, an int value or a constrained text type like varchar(30). In contrast, if you read a compound subvalue, it's most likely that you'll want it as a genuine json or jsonb value.

Summary:   ->   versus   ->>   and   #>   versus   #>>

  • Each of the -> and #> operators returns a genuine JSON value. When the input is json, the return value is json. And when the input is jsonb, the return value is jsonb.
  • Each of the ->> and #>> operators returns a genuine text value, both when the input is json, and when the input is jsonb.
  • If the value that the path denotes is a compound JSON value, then the >> variant returns the text representation of the JSON value, as specified by RFC 7159. (The designers of the PostgreSQL functionality had no other feasible choice.) But if the JSON value that the path denotes is primitive, then the >> variant produces the text representation of the value itself. It turns out that the text representation of a primitive JSON value and the text representation of the value itself differ only for a JSON string—exemplified by "a" versus a.

The following ASSERT tests all these rules:

do $body$
declare
  ta constant text     := 'a';
  tn constant numeric  := -1.7;
  ti constant numeric  := 42;
  tb constant boolean  := true;
  t2 constant text  := '["a", -1.7, 42, true, null]';

  j1 constant jsonb := '"a"';
  j2 constant jsonb := t2::jsonb;
  j3 constant jsonb := '{"p": 1, "q": ["a", -1.7, 42, true, null]}';
begin
  assert
    (j2 ->  0)               = j1 and
    (j2 ->> 0)               = ta and
    (j2 ->> 1)::numeric      = tn and
    (j2 ->> 2)::int          = ti and
    (j2 ->> 3)::boolean      = tb and
    (j2 ->> 4)            is null and

    (j3 #>  array['q', '0']) = j1 and
    (j3 #>> array['q', '0']) = ta and

    (j3 ->  'q')             = j2 and
    (j3 ->> 'q')             = t2
    ,
    'unexpected';
end;
$body$;
  • The   ->   operator
  • The   #>   operator
  • The   ->>   and   #>>   operators
  • Summary:   ->   versus   ->>   and   #>   versus   #>>
Ask our community
  • Slack
  • Github
  • Forum
  • StackOverflow
Yugabyte
Contact Us
Copyright © 2017-2020 Yugabyte, Inc. All rights reserved.