> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# JSONB
export const InternalLink = ({version, path = "", children, ...props}) => {
let detectedVersion = version || "stable";
if (typeof window !== 'undefined' && !version) {
const match = window.location.pathname.match(/\/docs\/([^/]+)/);
if (match) {
detectedVersion = match[1];
}
}
const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
return
{children}
;
};
The `JSONB` data type stores JSON (JavaScript Object Notation) data as a binary representation of the `JSONB` value, which eliminates whitespace, duplicate keys, and key ordering. `JSONB` supports GIN indexes.
For a hands-on demonstration of storing and querying JSON data from a third-party API, see the JSON tutorial.
## Alias
In CockroachDB, `JSON` is an alias for `JSONB`.
In PostgreSQL, `JSONB` and `JSON` are two different data types. In CockroachDB, the `JSONB` / `JSON` data type is similar in behavior to the [`JSONB` data type in PostgreSQL](https://www.postgresql.org/docs/current/static/datatype-json).
### Syntax
The syntax for the `JSONB` data type follows the format specified in [RFC8259](https://tools.ietf.org/html/rfc8259). You can express a constant value of type `JSONB` using an interpreted literal or a string literal annotated with type `JSONB`.
There are six types of `JSONB` values:
* `null`
* Boolean
* String
* Number (i.e., `decimal`, **not** the standard `int64` )
* Array (i.e., an ordered sequence of `JSONB` values)
* Object (i.e., a mapping from strings to `JSONB` values)
Examples:
* `'[{"foo":"bar"}]'`
* `'{"type": "account creation", "username": "harvestboy93"}'`
* `'{"first_name": "Ernie", "status": "Looking for treats", "location": "Brooklyn"}'`
* `'{"prices": [ { "05/01/2022": 100.5 }, { "06/01/2022": 101.5 } ]}'`
If duplicate keys are included in the input, only the last value is kept.
## Size
The size of a `JSONB` value is variable, but we recommend that you keep values under 1 MB to ensure satisfactory performance. Above that threshold, write amplification and other considerations may cause significant performance degradation.
We **strongly recommend** adding size limits to all indexed columns, which includes columns in primary keys.
Values exceeding 1 MiB can lead to storage layer write amplification and cause significant performance degradation or even crashes due to OOMs (out of memory errors).
To add a size limit using `CREATE TABLE`:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE TABLE name (first STRING(100), last STRING(100));
```
To add a size limit using `ALTER TABLE... ALTER COLUMN`:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER TABLE name ALTER first TYPE STRING(99);
```
## Operators
| Operator | Description | Example Query and Output | |
| ----------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------- | ------------------------------------------------------------- |
| `->` | Access a `JSONB` field, returning a `JSONB` value. | `SELECT '[{"foo":"bar"}]'::JSONB->0->'foo';` `"bar"::JSONB` | |
| `->>` | Access a `JSONB` field, returning a string. | `SELECT '{"foo":"bar"}'::JSONB->>'foo';` `bar::STRING` | |
| `@>` | Tests whether the left `JSONB` field contains the right `JSONB` field. | `SELECT ('{"foo": {"baz": 3}, "bar": 2}'::JSONB@>'{"foo": {"baz":3}}'::JSONB );` `true` | |
| `<@` | Tests whether the left `JSONB` field is contained by the right `JSONB` field. | `SELECT('{"bar":2}'::JSONB<@'{"foo":1, "bar":2}'::JSONB);` `true` | |
| `#>` | Access a `JSONB` field at the specified path, returning a `JSONB` value. | `SELECT '[{"foo":"bar"}]'::JSONB#>'{0,foo}';` `"bar"::JSONB` | |
| `#>>` | Access a `JSONB` field at the specified path, returning a string. | `SELECT '[{"foo":"bar"}]'::JSONB#>>'{0,foo}';` `bar::STRING` | |
| `?` | Does the key or element string exist within the JSONB value? | `SELECT('{"foo":1, "bar":2}'::JSONB?'bar');` `true` | |
| `?&` | Do all the key or element strings exist within the JSONB value? | `SELECT('{"foo":1, "bar":2}'::JSONB?&array['foo','bar']);` `true` | |
| \`? | \` | Do any of the key or element strings exist within the JSONB value? | `SELECT('{"foo":1, "bar":2}'::JSONB?\|array['bar']);` `true` |
| `[` ... `]` | Access a `JSONB` key, returning a `JSONB` value or object. For details, see Subscripted expressions. | `SELECT('{"foo": {"bar":1}}'::JSONB)['foo']['bar'];` `1` | |
For the full list of supported `JSONB` operators, see Operators.
## Functions
| Function | Description |
| --------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `jsonb\_array\_elements()` | Expands a `JSONB` array to a set of `JSONB` values. See [Map a `JSONB` array field into rows](#map-a-jsonb-array-field-into-rows). |
| `jsonb\_build\_object(...)` | Builds a `JSONB` object out of a variadic argument list that alternates between keys and values. |
| `jsonb\_each()` | Expands the outermost `JSONB` object into a set of key-value pairs. See [Retrieve key-value pairs from a `JSONB` field](#retrieve-key-value-pairs-from-a-jsonb-field). |
| `jsonb\_object\_keys()` | Returns sorted set of keys in the outermost `JSONB` object. See [Retrieve the distinct keys from a `JSONB` field](#retrieve-the-distinct-keys-from-a-jsonb-field). |
| `jsonb\_pretty()` | Returns the given `JSONB` value as a `STRING` indented and with newlines. See [Retrieve formatted `JSONB` data](#retrieve-formatted-jsonb-data). |
| `jsonb\_set(val: jsonb, path: string[], to: jsonb)` | Returns the JSON value pointed to by the variadic arguments. See [Update an array element](#update-an-array-element). |
For the full list of supported `JSONB` functions, see JSONB functions.
## Index `JSONB` data
To index a `JSONB` column you can use a GIN index or index an expression on the column.
## Known limitations
* You cannot use primary key, foreign key, and unique constraints on `JSONB` values.
## Examples
This section shows how to create tables with `JSONB` columns and use operators and functions to access and update `JSONB` data. For the full list of operators and functions, see Operators and JSONB functions.
### Create a table with a `JSONB` column
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE TABLE users (
profile_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
last_updated TIMESTAMP DEFAULT now(),
user_profile JSONB
);
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW COLUMNS FROM users;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
column_name | data_type | is_nullable | column_default | generation_expression | indices | is_hidden
---------------+-----------+-------------+-------------------+-----------------------+--------------+------------
profile_id | UUID | false | gen_random_uuid() | | {users_pkey} | false
last_updated | TIMESTAMP | true | now():::TIMESTAMP | | {users_pkey} | false
user_profile | JSONB | true | NULL | | {users_pkey} | false
(3 rows)
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
INSERT INTO users (user_profile) VALUES
('{"first_name": "Lola", "last_name": "Dog", "location": "NYC", "online" : true, "friends" : 547}'),
('{"first_name": "Ernie", "status": "Looking for treats", "location" : "Brooklyn"}');
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT * FROM users;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
+--------------------------------------+----------------------------------+----------------------------------------------------------------------------------------------+
| profile_id | last_updated | user_profile |
+--------------------------------------+----------------------------------+----------------------------------------------------------------------------------------------+
| 33c0a5d8-b93a-4161-a294-6121ee1ade93 | 2022-02-27 16:39:28.155024+00:00 | {"first_name": "Lola", "friends": 547, "last_name": "Dog", "location":"NYC", "online": true} |
| 6a7c15c9-462e-4551-9e93-f389cf63918a | 2022-02-27 16:39:28.155024+00:00 | {"first_name": "Ernie", "location": "Brooklyn", "status": "Looking for treats} |
+--------------------------------------+----------------------------------+----------------------------------------------------------------------------------------------+
```
### Retrieve formatted `JSONB` data
To retrieve `JSONB` data with easier-to-read formatting, use the `jsonb_pretty()` function. For example, retrieve data from the table you created in the [first example](#create-a-table-with-a-jsonb-column):
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT profile_id, last_updated, jsonb_pretty(user_profile) FROM users;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
+--------------------------------------+----------------------------------+------------------------------------+
| profile_id | last_updated | jsonb_pretty |
+--------------------------------------+----------------------------------+------------------------------------+
| 33c0a5d8-b93a-4161-a294-6121ee1ade93 | 2022-02-27 16:39:28.155024+00:00 | { |
| | | "first_name": "Lola", |
| | | "friends": 547, |
| | | "last_name": "Dog", |
| | | "location": "NYC", |
| | | "online": true |
| | | } |
| 6a7c15c9-462e-4551-9e93-f389cf63918a | 2022-02-27 16:39:28.155024+00:00 | { |
| | | "first_name": "Ernie", |
| | | "location": "Brooklyn", |
| | | "status": "Looking for treats" |
| | | } |
+--------------------------------------+----------------------------------+------------------------------------+
```
### Retrieve a specific field from `JSONB` data
To retrieve a specific field from `JSONB` data, use the `->` operator. For example, to retrieve a field from the table you created in [Create a table with a `JSONB` column](#create-a-table-with-a-jsonb-column), run:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT user_profile->'first_name',user_profile->'location' FROM users;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
?column? | ?column?
-----------+-------------
"Ernie" | "Brooklyn"
"Lola" | "NYC"
```
You can also use a subscripted expression for an equivalent result:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT (user_profile)['first_name'],(user_profile)['location'] FROM users;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
user_profile | user_profile
---------------+---------------
"Ernie" | "Brooklyn"
"Lola" | "NYC"
```
Use the `->>` operator to return `JSONB` fields as `STRING` values:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT user_profile->>'first_name', user_profile->>'location' FROM users;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
?column? | ?column?
-----------+-----------
Ernie | Brooklyn
Lola | NYC
```
Use the `@>` operator to filter the values in a field in a `JSONB` column:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT user_profile->'first_name', user_profile->'location' FROM users WHERE user_profile @> '{"location":"NYC"}';
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
?column? | ?column?
-----------+-----------
"Lola" | "NYC"
```
Use the `#>>` operator with a path to return all first names:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT user_profile#>>'{first_name}' as "first name" from users;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
first name
--------------
Ernie
Lola
(2 rows)
```
### Retrieve the distinct keys from a `JSONB` field
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT DISTINCT jsonb_object_keys(user_profile) AS keys FROM users;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
keys
--------------
first_name
friends
last_name
location
online
status
(6 rows)
```
### Retrieve key-value pairs from a `JSONB` field
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT jsonb_each(user_profile) AS pairs FROM users;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
pairs
-------------------------------------
(first_name,"""Lola""")
(friends,547)
(last_name,"""Dog""")
(location,"""NYC""")
(online,true)
(first_name,"""Ernie""")
(location,"""Brooklyn""")
(status,"""Looking for treats""")
(8 rows)
```
### Group and order `JSONB` values
To organize your `JSONB` field values, use the `GROUP BY` and `ORDER BY` clauses with the `->>` operator. For example, organize the `first_name` values from the table you created in the [first example](#create-a-table-with-a-jsonb-column):
For this example, we will add a few more records to the existing table. This will help us see clearly how the data is grouped.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
INSERT INTO users (user_profile) VALUES
('{"first_name": "Lola", "last_name": "Kim", "location": "Seoul", "online": false, "friends": 600}'),
('{"first_name": "Parvati", "last_name": "Patil", "location": "London", "online": false, "friends": 500}');
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT user_profile->>'first_name' AS first_name, user_profile->>'location' AS location FROM users;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
first_name | location
-------------+-----------
Ernie | Brooklyn
Lola | NYC
Parvati | London
Lola | Seoul
```
Group and order the data.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT user_profile->>'first_name' first_name, count(*) total FROM users group by user_profile->>'first_name' order by total;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
first_name | total
-------------+-------
Ernie | 1
Parvati | 1
Lola | 2
```
The `->>` operator returns `STRING` and uses string comparison rules to order the data. If you want numeric ordering, [cast the resulting data](#supported-casting-and-conversion) to `FLOAT`.
### Map a `JSONB` array field into rows
To map a `JSONB` array field into rows, use the `jsonb_array_elements` function:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE TABLE commodity (id varchar(10), data jsonb);
INSERT INTO commodity (id, data) values ('silver', '{"prices" : [ { "05/01/2022" : 100.5 } , { "06/01/2022" : 101.5 } ]}');
INSERT INTO commodity (id, data) values ('gold', '{"prices" : [ { "05/01/2022" : 200.5 } , { "06/01/2022" : 211.5 } ]}');
SELECT * FROM commodity;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
id | data
---------+-------------------------------------------------------------
silver | {"prices": [{"05/01/2022": 100.5}, {"06/01/2022": 101.5}]}
gold | {"prices": [{"05/01/2022": 200.5}, {"06/01/2022": 211.5}]}
(2 rows)
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT id as commodity, jsonb_array_elements(commodity.data->'prices') AS "price" FROM commodity;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
commodity | price
------------+------------------------
silver | {"05/01/2022": 100.5}
silver | {"06/01/2022": 101.5}
gold | {"05/01/2022": 200.5}
gold | {"06/01/2022": 211.5}
(4 rows)
```
### Access nested `JSONB` fields
To display the commodity prices for May, run:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT id AS commodity, data->'prices'->0->'05/01/2022' AS "May prices" from commodity;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
commodity | May prices
------------+-------------
silver | 100.5
gold | 200.5
(2 rows)
```
### Update an array element
To update a field value, use the `jsonb_set` function. For example, to update the price of `silver` on `06/01/2022` to `90.5`, run:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
UPDATE commodity SET data = jsonb_set(data, '{prices, 1, "06/01/2022"}', '90.5') where id = 'silver';
UPDATE 1
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT * FROM commodity;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
id | data
---------+-------------------------------------------------------------
silver | {"prices": [{"05/01/2022": 100.5}, {"06/01/2022": 90.5}]}
gold | {"prices": [{"05/01/2022": 200.5}, {"06/01/2022": 211.5}]}
(2 rows)
```
### Create a table with a `JSONB` column and a computed column
In this example, create a table with a `JSONB` column and a stored computed column:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE student_profiles (
id STRING PRIMARY KEY AS (profile->>'id') STORED,
profile JSONB
);
```
Create a compute column after you create a table:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> ALTER TABLE student_profiles ADD COLUMN age INT AS ( (profile->>'age')::INT) STORED;
```
Then, insert a few rows of data:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO student_profiles (profile) VALUES
('{"id": "d78236", "name": "Arthur Read", "age": "16", "school": "PVPHS", "credits": 120, "sports": "none"}'),
('{"name": "Buster Bunny", "age": "15", "id": "f98112", "school": "THS", "credits": 67, "clubs": "MUN"}'),
('{"name": "Ernie Narayan", "school" : "Brooklyn Tech", "id": "t63512", "sports": "Track and Field", "clubs": "Chess"}');
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM student_profiles;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
+--------+---------------------------------------------------------------------------------------------------------------------+------+
| id | profile | age |
---------+---------------------------------------------------------------------------------------------------------------------+------+
| d78236 | {"age": "16", "credits": 120, "id": "d78236", "name": "Arthur Read", "school": "PVPHS", "sports": "none"} | 16 |
| f98112 | {"age": "15", "clubs": "MUN", "credits": 67, "id": "f98112", "name": "Buster Bunny", "school": "THS"} | 15 |
| t63512 | {"clubs": "Chess", "id": "t63512", "name": "Ernie Narayan", "school": "Brooklyn Tech", "sports": "Track and Field"} | NULL |
+--------+---------------------------------------------------------------------------------------------------------------------+------|
```
The primary key `id` is computed as a field from the `profile` column. Additionally the `age` column is computed from the profile column data as well.
This example shows how add a stored computed column with a coerced type:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE TABLE json_data (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
json_info JSONB
);
INSERT INTO json_data (json_info) VALUES ('{"amount": "123.45"}');
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER TABLE json_data ADD COLUMN amount DECIMAL AS ((json_info->>'amount')::DECIMAL) STORED;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT * FROM json_data;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
id | json_info | amount
---------------------------------------+----------------------+---------
e7c3d706-1367-4d77-bfb4-386dfdeb10f9 | {"amount": "123.45"} | 123.45
(1 row)
```
### Create a table with a `JSONB` column and a virtual computed column
In this example, create a table with a `JSONB` column and virtual computed columns:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE student_profiles (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
profile JSONB,
full_name STRING AS (concat_ws(' ',profile->>'firstName', profile->>'lastName')) VIRTUAL,
birthday TIMESTAMP AS (parse_timestamp(profile->>'birthdate')) VIRTUAL
);
```
Then, insert a few rows of data:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO student_profiles (profile) VALUES
('{"id": "d78236", "firstName": "Arthur", "lastName": "Read", "birthdate": "2010-01-25", "school": "PVPHS", "credits": 120, "sports": "none"}'),
('{"firstName": "Buster", "lastName": "Bunny", "birthdate": "2011-11-07", "id": "f98112", "school": "THS", "credits": 67, "clubs": "MUN"}'),
('{"firstName": "Ernie", "lastName": "Narayan", "school" : "Brooklyn Tech", "id": "t63512", "sports": "Track and Field", "clubs": "Chess"}');
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM student_profiles;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
id | profile | full_name | birthday
---------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------+---------------+----------------------
0e420282-105d-473b-83e2-3b082e7033e4 | {"birthdate": "2011-11-07", "clubs": "MUN", "credits": 67, "firstName": "Buster", "id": "f98112", "lastName": "Bunny", "school": "THS"} | Buster Bunny | 2011-11-07 00:00:00
6e9b77cd-ec67-41ae-b346-7b3d89902c72 | {"birthdate": "2010-01-25", "credits": 120, "firstName": "Arthur", "id": "d78236", "lastName": "Read", "school": "PVPHS", "sports": "none"} | Arthur Read | 2010-01-25 00:00:00
f74b21e3-dc1e-49b7-a648-3c9b9024a70f | {"clubs": "Chess", "firstName": "Ernie", "id": "t63512", "lastName": "Narayan", "school": "Brooklyn Tech", "sports": "Track and Field"} | Ernie Narayan | NULL
(3 rows)
Time: 2ms total (execution 2ms / network 0ms)
```
The virtual column `full_name` is computed as a field from the `profile` column's data. The first name and last name are concatenated and separated by a single whitespace character using the `concat_ws` string function.
The virtual column `birthday` is parsed as a `TIMESTAMP` value from the `profile` column's `birthdate` string value. The `parse_timestamp` function is used to parse strings in `TIMESTAMP` format.
## Supported casting and conversion
This section describes how to cast and convert `JSONB` values.
You can cast all `JSONB` values to the following data type:
* `STRING`
You can cast numeric `JSONB` values to the following numeric data types:
* `DECIMAL`
* `FLOAT`
* `INT`
For example:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT '100'::JSONB::INT;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
int8
--------
100
(1 row)
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT '100000'::JSONB::FLOAT;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
float8
----------
100000
(1 row)
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT '100.50'::JSONB::DECIMAL;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
numeric
-----------
100.50
(1 row)
```
You can use the `parse_timestamp` function to parse strings in `TIMESTAMP` format.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT parse_timestamp ('2022-05-28T10:53:25.160Z');
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
parse_timestamp
--------------------------
2022-05-28 10:53:25.16
(1 row)
```
You can use the `parse_timestamp` function to retrieve string representations of timestamp data within `JSONB` columns in `TIMESTAMP` format.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE TABLE events (
raw JSONB,
event_created TIMESTAMP AS (parse_timestamp(raw->'event'->>'created')) VIRTUAL
);
INSERT INTO events (raw) VALUES ('{"event":{"created":"2022-05-28T10:53:25.160Z"}}');
SELECT event_created FROM events;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE TABLE
INSERT 1
event_created
--------------------------
2022-05-28 10:53:25.16
(1 row)
```
## See also
* JSON tutorial
* GIN Indexes
* Use an expression to index a field in a JSONB column
* Computed Columns
* Data Types
* Functions and Operators