esqueleto-3.6.0.0: Type-safe EDSL for SQL queries on persistent backends.

Safe Haskell | None
Language | Haskell2010

Database.Esqueleto.PostgreSQL.JSON

Contents

- JSONB Newtype
- JSONAccessor
- Arrow operators
- Filter operators
- Deletion and concatenation operators

Description

This module contains PostgreSQL-specific JSON functions.

A couple of things to keep in mind about this module:

- The `Type` column in the PostgreSQL documentation tables are the types of the right operand, the left is always `jsonb`.
- Since these operators can all take `NULL` values as their input, and most can also output `NULL` values (even when the inputs are guaranteed to not be NULL), all `JSONB` values are wrapped in `Maybe`. This also makes it easier to chain them. (cf. `JSONBExpr`) Just use the `just` function to lift any non-`Maybe` JSONB values in case it doesn't type check.
- As long as the previous operator's resulting value is a `JSONBExpr`, any other JSON operator can be used to transform the JSON further. (e.g. `[1,2,3] -> 1 @> 2`)

The PostgreSQL version the functions work with are included in their description.

Since: 3.1.0

Synopsis

- newtype JSONB a = JSONB {
  - unJSONB :: a
  }
- type JSONBExpr a = SqlExpr (Value (Maybe (JSONB a)))
- jsonbVal :: (FromJSON a, ToJSON a) => a -> JSONBExpr a
- data JSONAccessor
  - = JSONIndex Int
  - | JSONKey Text
- (->.) :: JSONBExpr a -> JSONAccessor -> JSONBExpr b
- (->>.) :: JSONBExpr a -> JSONAccessor -> SqlExpr (Value (Maybe Text))
- (#>.) :: JSONBExpr a -> [Text] -> JSONBExpr b
- (#>>.) :: JSONBExpr a -> [Text] -> SqlExpr (Value (Maybe Text))
- (@>.) :: JSONBExpr a -> JSONBExpr b -> SqlExpr (Value Bool)
- (<@.) :: JSONBExpr a -> JSONBExpr b -> SqlExpr (Value Bool)
- (?.) :: JSONBExpr a -> Text -> SqlExpr (Value Bool)
- (?|.) :: JSONBExpr a -> [Text] -> SqlExpr (Value Bool)
- (?&.) :: JSONBExpr a -> [Text] -> SqlExpr (Value Bool)
- (-.) :: JSONBExpr a -> JSONAccessor -> JSONBExpr b
- (--.) :: JSONBExpr a -> [Text] -> JSONBExpr b
- (#-.) :: JSONBExpr a -> [Text] -> JSONBExpr b
- (||.) :: JSONBExpr a -> JSONBExpr b -> JSONBExpr c

# JSONB Newtype

With `JSONB`, you can use your Haskell types in your database table models as long as your type has `FromJSON` and `ToJSON` instances.

    import Database.Persist.TH

    share [mkPersist sqlSettings, mkMigrate "migrateAll"] [persistUpperCase|
      Example
        json (JSONB MyType)
    |]

CAUTION: Remember that changing the `FromJSON` instance of your type might result in old data becoming unparsable! You can use (`JSONB Data.Aeson.Value`) for unstructured/variable JSON.

newtype JSONB a Source #

Newtype wrapper around any type with a JSON representation.

Since: 3.1.0

Constructors

JSONB |

Fields

- unJSONB :: a

#### Instances

Instances details

Functor JSONB Source # |
Instance details Defined in Database.Esqueleto.PostgreSQL.JSON.Instances Methods fmap :: (a -> b) -> JSONB a -> JSONB b Source # (<$) :: a -> JSONB b -> JSONB a Source #
Foldable JSONB Source # |

Instance details

Defined in Database.Esqueleto.PostgreSQL.JSON.Instances

Methods

fold :: Monoid m => JSONB m -> m Source #

foldMap :: Monoid m => (a -> m) -> JSONB a -> m Source #

foldMap' :: Monoid m => (a -> m) -> JSONB a -> m Source #

foldr :: (a -> b -> b) -> b -> JSONB a -> b Source #

foldr' :: (a -> b -> b) -> b -> JSONB a -> b Source #

foldl :: (b -> a -> b) -> b -> JSONB a -> b Source #

foldl' :: (b -> a -> b) -> b -> JSONB a -> b Source #

foldr1 :: (a -> a -> a) -> JSONB a -> a Source #

foldl1 :: (a -> a -> a) -> JSONB a -> a Source #

toList :: JSONB a -> [a] Source #

null :: JSONB a -> Bool Source #

length :: JSONB a -> Int Source #

elem :: Eq a => a -> JSONB a -> Bool Source #

maximum :: Ord a => JSONB a -> a Source #

minimum :: Ord a => JSONB a -> a Source #

sum :: Num a => JSONB a -> a Source #

product :: Num a => JSONB a -> a Source #

Traversable JSONB Source # |
Instance details Defined in Database.Esqueleto.PostgreSQL.JSON.Instances Methods traverse :: Applicative f => (a -> f b) -> JSONB a -> f (JSONB b) Source # sequenceA :: Applicative f => JSONB (f a) -> f (JSONB a) Source # mapM :: Monad m => (a -> m b) -> JSONB a -> m (JSONB b) Source # sequence :: Monad m => JSONB (m a) -> m (JSONB a) Source #
FromJSON a => FromJSON (JSONB a) Source # |
Instance details Defined in Database.Esqueleto.PostgreSQL.JSON.Instances Methods parseJSON :: Value -> Parser (JSONB a) Source # parseJSONList :: Value -> Parser [JSONB a] Source # omittedField :: Maybe (JSONB a) Source #
ToJSON a => ToJSON (JSONB a) Source # |
Instance details Defined in Database.Esqueleto.PostgreSQL.JSON.Instances Methods toJSON :: JSONB a -> Value Source # toEncoding :: JSONB a -> Encoding Source # toJSONList :: [JSONB a] -> Value Source # toEncodingList :: [JSONB a] -> Encoding Source # omitField :: JSONB a -> Bool Source #
Generic (JSONB a) Source # |

Instance details

Defined in Database.Esqueleto.PostgreSQL.JSON.Instances

Associated Types

type Rep (JSONB a) |
Instance details Defined in Database.Esqueleto.PostgreSQL.JSON.Instances type Rep (JSONB a) = D1 ('MetaData "JSONB" "Database.Esqueleto.PostgreSQL.JSON.Instances" "esqueleto-3.6.0.0-Ge0SEUvHQ57EVeQZglAZgs" 'True) (C1 ('MetaCons "JSONB" 'PrefixI 'True) (S1 ('MetaSel ('Just "unJSONB") 'NoSourceUnpackedness 'NoSourceStrictness 'DecidedLazy) (Rec0 a)))

Methods

from :: JSONB a -> Rep (JSONB a) x Source #

to :: Rep (JSONB a) x -> JSONB a Source #

Read a => Read (JSONB a) Source # |
Instance details Defined in Database.Esqueleto.PostgreSQL.JSON.Instances Methods readsPrec :: Int -> ReadS (JSONB a) Source # readList :: ReadS [JSONB a] Source # readPrec :: ReadPrec (JSONB a) Source # readListPrec :: ReadPrec [JSONB a] Source #
Show a => Show (JSONB a) Source # |
Instance details Defined in Database.Esqueleto.PostgreSQL.JSON.Instances Methods showsPrec :: Int -> JSONB a -> ShowS Source # show :: JSONB a -> String Source # showList :: [JSONB a] -> ShowS Source #
Eq a => Eq (JSONB a) Source # |
Instance details Defined in Database.Esqueleto.PostgreSQL.JSON.Instances Methods (==) :: JSONB a -> JSONB a -> Bool Source # (/=) :: JSONB a -> JSONB a -> Bool Source #
Ord a => Ord (JSONB a) Source # |
Instance details Defined in Database.Esqueleto.PostgreSQL.JSON.Instances Methods compare :: JSONB a -> JSONB a -> Ordering Source # (<) :: JSONB a -> JSONB a -> Bool Source # (<=) :: JSONB a -> JSONB a -> Bool Source # (>) :: JSONB a -> JSONB a -> Bool Source # (>=) :: JSONB a -> JSONB a -> Bool Source # max :: JSONB a -> JSONB a -> JSONB a Source # min :: JSONB a -> JSONB a -> JSONB a Source #
(FromJSON a, ToJSON a) => PersistField (JSONB a) Source # | Since: 3.1.0
Instance details Defined in Database.Esqueleto.PostgreSQL.JSON.Instances Methods toPersistValue :: JSONB a -> PersistValue Source # fromPersistValue :: PersistValue -> Either Text (JSONB a) Source #
(FromJSON a, ToJSON a) => PersistFieldSql (JSONB a) Source # | jsonb Since: 3.1.0
Instance details Defined in Database.Esqueleto.PostgreSQL.JSON.Instances Methods sqlType :: Proxy (JSONB a) -> SqlType Source #
type Rep (JSONB a) Source # |
Instance details Defined in Database.Esqueleto.PostgreSQL.JSON.Instances type Rep (JSONB a) = D1 ('MetaData "JSONB" "Database.Esqueleto.PostgreSQL.JSON.Instances" "esqueleto-3.6.0.0-Ge0SEUvHQ57EVeQZglAZgs" 'True) (C1 ('MetaCons "JSONB" 'PrefixI 'True) (S1 ('MetaSel ('Just "unJSONB") 'NoSourceUnpackedness 'NoSourceStrictness 'DecidedLazy) (Rec0 a)))

type JSONBExpr a = SqlExpr (Value (Maybe (JSONB a))) Source #

`SqlExpr` of a NULL-able `JSONB` value. Hence the `Maybe`.

Note: NULL here is a PostgreSQL NULL, not a JSON `null`

jsonbVal :: (FromJSON a, ToJSON a) => a -> JSONBExpr a Source #

Convenience function to lift a regular value into a `JSONB` expression.

# JSONAccessor

data JSONAccessor Source #

Used with certain JSON operators.

This data type has `Num` and `IsString` instances for ease of use by using integer and string literals.

    >>> 3 :: JSONAccessor
    JSONIndex 3
    >>> -3 :: JSONAccessor
    JSONIndex -3

    >>> "name" :: JSONAccessor
    JSONKey "name"

NOTE: DO NOT USE ANY OF THE `Num` METHODS ON THIS TYPE!

Constructors

JSONIndex Int |
JSONKey Text |

#### Instances

Instances details

IsString JSONAccessor Source # |
Instance details Defined in Database.Esqueleto.PostgreSQL.JSON.Instances Methods fromString :: String -> JSONAccessor Source #
Generic JSONAccessor Source # |

Instance details

Defined in Database.Esqueleto.PostgreSQL.JSON.Instances

Associated Types

type Rep JSONAccessor |

Instance details

Defined in Database.Esqueleto.PostgreSQL.JSON.Instances

type Rep JSONAccessor = D1 ('MetaData "JSONAccessor" "Database.Esqueleto.PostgreSQL.JSON.Instances" "esqueleto-3.6.0.0-Ge0SEUvHQ57EVeQZglAZgs" 'False) (C1 ('MetaCons "JSONIndex" 'PrefixI 'False) (S1 ('MetaSel ('Nothing :: Maybe Symbol) 'NoSourceUnpackedness 'NoSourceStrictness 'DecidedLazy) (Rec0 Int)) :+: C1 ('MetaCons "JSONKey" 'PrefixI 'False) (S1 ('MetaSel ('Nothing :: Maybe Symbol) 'NoSourceUnpackedness 'NoSourceStrictness 'DecidedLazy) (Rec0 Text)))

Methods

from :: JSONAccessor -> Rep JSONAccessor x Source #

to :: Rep JSONAccessor x -> JSONAccessor Source #

Num JSONAccessor Source # | I repeat, DO NOT use any method other than `fromInteger`!

Instance details

Defined in Database.Esqueleto.PostgreSQL.JSON.Instances

Methods

(+) :: JSONAccessor -> JSONAccessor -> JSONAccessor Source #

(-) :: JSONAccessor -> JSONAccessor -> JSONAccessor Source #

(*) :: JSONAccessor -> JSONAccessor -> JSONAccessor Source #

negate :: JSONAccessor -> JSONAccessor Source #

abs :: JSONAccessor -> JSONAccessor Source #

signum :: JSONAccessor -> JSONAccessor Source #

fromInteger :: Integer -> JSONAccessor Source #

Show JSONAccessor Source # |
Instance details Defined in Database.Esqueleto.PostgreSQL.JSON.Instances Methods showsPrec :: Int -> JSONAccessor -> ShowS Source # show :: JSONAccessor -> String Source # showList :: [JSONAccessor] -> ShowS Source #
Eq JSONAccessor Source # |
Instance details Defined in Database.Esqueleto.PostgreSQL.JSON.Instances Methods (==) :: JSONAccessor -> JSONAccessor -> Bool Source # (/=) :: JSONAccessor -> JSONAccessor -> Bool Source #
type Rep JSONAccessor Source # |

Instance details

Defined in Database.Esqueleto.PostgreSQL.JSON.Instances

type Rep JSONAccessor = D1 ('MetaData "JSONAccessor" "Database.Esqueleto.PostgreSQL.JSON.Instances" "esqueleto-3.6.0.0-Ge0SEUvHQ57EVeQZglAZgs" 'False) (C1 ('MetaCons "JSONIndex" 'PrefixI 'False) (S1 ('MetaSel ('Nothing :: Maybe Symbol) 'NoSourceUnpackedness 'NoSourceStrictness 'DecidedLazy) (Rec0 Int)) :+: C1 ('MetaCons "JSONKey" 'PrefixI 'False) (S1 ('MetaSel ('Nothing :: Maybe Symbol) 'NoSourceUnpackedness 'NoSourceStrictness 'DecidedLazy) (Rec0 Text)))

# Arrow operators

Better documentation included with individual functions

The arrow operators are selection functions to select values from JSON arrays or objects.

### PostgreSQL Documentation

Requires PostgreSQL version >= 9.3

         | Type   | Description                                |  Example                                         | Example Result
    -----+--------+--------------------------------------------+--------------------------------------------------+----------------
     ->  | int    | Get JSON array element (indexed from zero, | '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2 | {"c":"baz"}
         |        | negative integers count from the end)      |                                                  |
     ->  | text   | Get JSON object field by key               | '{"a": {"b":"foo"}}'::json->a                  | {"b":"foo"}
     ->> | int    | Get JSON array element as text             | '[1,2,3]'::json->>2                              | 3
     ->> | text   | Get JSON object field as text              | '{"a":1,"b":2}'::json->>b                      | 2
     #>  | text[] | Get JSON object at specified path          | '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}'       | {"c": "foo"}
     #>> | text[] | Get JSON object at specified path as text  | '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'      | 3

(->.) :: JSONBExpr a -> JSONAccessor -> JSONBExpr b infixl 6 Source #

Requires PostgreSQL version >= 9.3

This function extracts the jsonb value from a JSON array or object, depending on whether you use an `int` or a `text`. (cf. `JSONAccessor`)

As long as the left operand is `jsonb`, this function will not throw an exception, but will return `NULL` when an `int` is used on anything other than a JSON array, or a `text` is used on anything other than a JSON object.

### PostgreSQL Documentation

Expand

        | Type | Description                                |  Example                                         | Example Result
    ----+------+--------------------------------------------+--------------------------------------------------+----------------
     -> | int  | Get JSON array element (indexed from zero) | '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2 | {"c":"baz"}
     -> | text | Get JSON object field by key               | '{"a": {"b":"foo"}}'::json->a                  | {"b":"foo"}

Since: 3.1.0

(->>.) :: JSONBExpr a -> JSONAccessor -> SqlExpr (Value (Maybe Text)) infixl 6 Source #

Requires PostgreSQL version >= 9.3

Identical to `->.`, but the resulting DB type is a `text`, so it could be chained with anything that uses `text`.

CAUTION: if the "scalar" JSON value `null` is the result of this function, PostgreSQL will interpret it as a PostgreSQL `NULL` value, and will therefore be `Nothing` instead of (Just "null")

### PostgreSQL Documentation

Expand

         | Type | Description                    |  Example                    | Example Result
    -----+------+--------------------------------+-----------------------------+----------------
     ->> | int  | Get JSON array element as text | '[1,2,3]'::json->>2         | 3
     ->> | text | Get JSON object field as text  | '{"a":1,"b":2}'::json->>b | 2

Since: 3.1.0

(#>.) :: JSONBExpr a -> [Text] -> JSONBExpr b infixl 6 Source #

Requires PostgreSQL version >= 9.3

This operator can be used to select a JSON value from deep inside another one. It only works on objects and arrays and will result in `NULL` (`Nothing`) when encountering any other JSON type.

The `Text`s used in the right operand list will always select an object field, but can also select an index from a JSON array if that text is parsable as an integer.

Consider the following:

    x ^. TestBody #>. ["0","1"]

The following JSON values in the `test` table's `body` column will be affected:

     Values in column                     | Resulting value
    --------------------------------------+----------------------------
    {"0":{"1":"Got it!"}}                 | "Got it!"
    {"0":[null,["Got it!","Even here!"]]} | ["Got it!", "Even here!"]
    [{"1":"Got it again!"}]               | "Got it again!"
    [[null,{"Wow":"so deep!"}]]           | {"Wow": "so deep!"}
    false                                 | NULL
    "nope"                                | NULL
    3.14                                  | NULL

### PostgreSQL Documentation

Expand

         | Type   | Description                       |  Example                                   | Example Result
    -----+--------+-----------------------------------+--------------------------------------------+----------------
     #>  | text[] | Get JSON object at specified path | '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}' | {"c": "foo"}

Since: 3.1.0

(#>>.) :: JSONBExpr a -> [Text] -> SqlExpr (Value (Maybe Text)) infixl 6 Source #

Requires PostgreSQL version >= 9.3

This function is to `#>.` as `->>.` is to `->.`

CAUTION: if the "scalar" JSON value `null` is the result of this function, PostgreSQL will interpret it as a PostgreSQL `NULL` value, and will therefore be `Nothing` instead of (Just "null")

### PostgreSQL Documentation

Expand

         | Type   | Description                               |  Example                                    | Example Result
    -----+--------+-------------------------------------------+---------------------------------------------+----------------
     #>> | text[] | Get JSON object at specified path as text | '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}' | 3

Since: 3.1.0

# Filter operators

Better documentation included with individual functions

These functions test certain properties of JSON values and return booleans, so are mainly used in WHERE clauses.

### PostgreSQL Documentation

Requires PostgreSQL version >= 9.4

        | Type   | Description                                                     |  Example
    ----+--------+-----------------------------------------------------------------+---------------------------------------------------
     @> | jsonb  | Does the left JSON value contain within it the right value?     | '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb
     <@ | jsonb  | Is the left JSON value contained within the right value?        | '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb
     ?  | text   | Does the string exist as a top-level key within the JSON value? | '{"a":1, "b":2}'::jsonb ? b
     ?| | text[] | Do any of these array strings exist as top-level keys?          | '{"a":1, "b":2, "c":3}'::jsonb ?| array[b, c]
     ?& | text[] | Do all of these array strings exist as top-level keys?          | '["a", "b"]'::jsonb ?& array[a, b]

(@>.) :: JSONBExpr a -> JSONBExpr b -> SqlExpr (Value Bool) infixl 6 Source #

Requires PostgreSQL version >= 9.4

This operator checks for the JSON value on the right to be a subset of the JSON value on the left.

Examples of the usage of this operator can be found in the Database.Persist.Postgresql.JSON module.

(here: https://hackage.haskell.org/package/persistent-postgresql-2.10.0/docs/Database-Persist-Postgresql-JSON.html)

### PostgreSQL Documentation

Expand

        | Type  | Description                                                 |  Example
    ----+-------+-------------------------------------------------------------+---------------------------------------------
     @> | jsonb | Does the left JSON value contain within it the right value? | '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb

Since: 3.1.0

(<@.) :: JSONBExpr a -> JSONBExpr b -> SqlExpr (Value Bool) infixl 6 Source #

Requires PostgreSQL version >= 9.4

This operator works the same as `@>.`, just with the arguments flipped. So it checks for the JSON value on the left to be a subset of JSON value on the right.

Examples of the usage of this operator can be found in the Database.Persist.Postgresql.JSON module.

(here: https://hackage.haskell.org/package/persistent-postgresql-2.10.0/docs/Database-Persist-Postgresql-JSON.html)

### PostgreSQL Documentation

Expand

        | Type  | Description                                              |  Example
    ----+-------+----------------------------------------------------------+---------------------------------------------
     <@ | jsonb | Is the left JSON value contained within the right value? | '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb

Since: 3.1.0

(?.) :: JSONBExpr a -> Text -> SqlExpr (Value Bool) infixl 6 Source #

Requires PostgreSQL version >= 9.4

This operator checks if the given text is a top-level member of the JSON value on the left. This means a top-level field in an object, a top-level string in an array or just a string value.

Examples of the usage of this operator can be found in the Database.Persist.Postgresql.JSON module.

(here: https://hackage.haskell.org/package/persistent-postgresql-2.10.0/docs/Database-Persist-Postgresql-JSON.html)

### PostgreSQL Documentation

Expand

       | Type | Description                                                     |  Example
    ---+------+-----------------------------------------------------------------+-------------------------------
     ? | text | Does the string exist as a top-level key within the JSON value? | '{"a":1, "b":2}'::jsonb ? b

Since: 3.1.0

(?|.) :: JSONBExpr a -> [Text] -> SqlExpr (Value Bool) infixl 6 Source #

Requires PostgreSQL version >= 9.4

This operator checks if ANY of the given texts is a top-level member of the JSON value on the left. This means any top-level field in an object, any top-level string in an array or just a string value.

Examples of the usage of this operator can be found in the Database.Persist.Postgresql.JSON module.

(here: https://hackage.haskell.org/package/persistent-postgresql-2.10.0/docs/Database-Persist-Postgresql-JSON.html)

### PostgreSQL Documentation

Expand

        | Type   | Description                                            |  Example
    ----+--------+--------------------------------------------------------+---------------------------------------------------
     ?| | text[] | Do any of these array strings exist as top-level keys? | '{"a":1, "b":2, "c":3}'::jsonb ?| array[b, c]

Since: 3.1.0

(?&.) :: JSONBExpr a -> [Text] -> SqlExpr (Value Bool) infixl 6 Source #

Requires PostgreSQL version >= 9.4

This operator checks if ALL of the given texts are top-level members of the JSON value on the left. This means a top-level field in an object, a top-level string in an array or just a string value.

Examples of the usage of this operator can be found in the Database.Persist.Postgresql.JSON module.

(here: https://hackage.haskell.org/package/persistent-postgresql-2.10.0/docs/Database-Persist-Postgresql-JSON.html)

### PostgreSQL Documentation

Expand

        | Type   | Description                                            |  Example
    ----+--------+--------------------------------------------------------+----------------------------------------
     ?& | text[] | Do all of these array strings exist as top-level keys? | '["a", "b"]'::jsonb ?& array[a, b]

Since: 3.1.0

# Deletion and concatenation operators

Better documentation included with individual functions

These operators change the shape of the JSON value and also have the highest risk of throwing an exception. Please read the descriptions carefully before using these functions.

### PostgreSQL Documentation

Requires PostgreSQL version >= 9.5

        | Type    | Description                                                            |  Example
    ----+---------+------------------------------------------------------------------------+-------------------------------------------------
     || | jsonb   | Concatenate two jsonb values into a new jsonb value                    | '["a", "b"]'::jsonb || '["c", "d"]'::jsonb
     -  | text    | Delete key/value pair or string element from left operand.             | '{"a": "b"}'::jsonb - a
        |         | Key/value pairs are matched based on their key value.                  |
     -  | integer | Delete the array element with specified index (Negative integers count | '["a", "b"]'::jsonb - 1
        |         | from the end). Throws an error if top level container is not an array. |
     #- | text[]  | Delete the field or element with specified path                        | '["a", {"b":1}]'::jsonb #- '{1,b}'
        |         | (for JSON arrays, negative integers count from the end)                |

Requires PostgreSQL version >= 10

        | Type    | Description                                                            |  Example
    ----+---------+------------------------------------------------------------------------+-------------------------------------------------
     -  | text[]  | Delete multiple key/value pairs or string elements from left operand.  | '{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[]
        |         | Key/value pairs are matched based on their key value.                  |

(-.) :: JSONBExpr a -> JSONAccessor -> JSONBExpr b infixl 6 Source #

Requires PostgreSQL version >= 9.5

This operator can remove a key from an object or a string element from an array when using text, and remove certain elements by index from an array when using integers.

Negative integers delete counting from the end of the array. (e.g. `-1` being the last element, `-2` being the second to last, etc.)

CAUTION: THIS FUNCTION THROWS AN EXCEPTION WHEN USED ON ANYTHING OTHER THAN OBJECTS OR ARRAYS WHEN USING TEXT, AND ANYTHING OTHER THAN ARRAYS WHEN USING INTEGERS!

### Objects and arrays

Expand

    {"a": 3.14}            - "a"         == {}
    {"a": "b"}             - "b"         == {"a": "b"}
    {"a": 3.14}            - "a"         == {}
    {"a": 3.14, "c": true} - "a"         == {"c": true}
    ["a", 2, "c"]          - "a"         == [2, "c"] -- can remove strings from arrays
    [true, "b", 5]         - 0           == ["b", 5]
    [true, "b", 5]         - 3           == [true, "b", 5]
    [true, "b", 5]         - -1          == [true, "b"]
    [true, "b", 5]         - -4          == [true, "b", 5]
    []                     - 1           == []
    {"1": true}            - 1           == ERROR: cannot delete from object using integer index
    1                      - <anything>  == ERROR: cannot delete from scalar
    "a"                    - <anything>  == ERROR: cannot delete from scalar
    true                   - <anything>  == ERROR: cannot delete from scalar
    null                   - <anything>  == ERROR: cannot delete from scalar

### PostgreSQL Documentation

Expand

       | Type    | Description                                                            |  Example
    ---+---------+------------------------------------------------------------------------+-------------------------------------------------
     - | text    | Delete key/value pair or string element from left operand.             | '{"a": "b"}'::jsonb - a
       |         | Key/value pairs are matched based on their key value.                  |
     - | integer | Delete the array element with specified index (Negative integers count | '["a", "b"]'::jsonb - 1
       |         | from the end). Throws an error if top level container is not an array. |

Since: 3.1.0

(--.) :: JSONBExpr a -> [Text] -> JSONBExpr b infixl 6 Source #

Requires PostgreSQL version >= 10

Removes a set of keys from an object, or string elements from an array.

This is the same operator internally as `-.`, but the option to use a `text array`, instead of `text` or `integer` was only added in version 10. That's why this function is seperate from `-.`

NOTE: The following is equivalent:

    {some JSON expression} -. "a" -. "b"

is equivalent to

    {some JSON expression} --. ["a","b"]

### PostgreSQL Documentation

Expand

       | Type    | Description                                                            |  Example
    ---+---------+------------------------------------------------------------------------+-------------------------------------------------
     - | text[]  | Delete multiple key/value pairs or string elements from left operand.  | '{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[]
       |         | Key/value pairs are matched based on their key value.                  |

Since: 3.1.0

(#-.) :: JSONBExpr a -> [Text] -> JSONBExpr b infixl 6 Source #

Requires PostgreSQL version >= 9.5

This operator can remove elements nested in an object.

If a `Text` is not parsable as a number when selecting in an array (even when halfway through the selection) an exception will be thrown.

Negative integers delete counting from the end of an array. (e.g. `-1` being the last element, `-2` being the second to last, etc.)

CAUTION: THIS FUNCTION THROWS AN EXCEPTION WHEN USED ON ANYTHING OTHER THAN OBJECTS OR ARRAYS, AND WILL ALSO THROW WHEN TRYING TO SELECT AN ARRAY ELEMENT WITH A NON-INTEGER TEXT

### Objects

Expand

    {"a": 3.14, "b": null}        #- []        == {"a": 3.14, "b": null}
    {"a": 3.14, "b": null}        #- ["a"]     == {"b": null}
    {"a": 3.14, "b": null}        #- ["a","b"] == {"a": 3.14, "b": null}
    {"a": {"b":false}, "b": null} #- ["a","b"] == {"a": {}, "b": null}

### Arrays

Expand

    [true, {"b":null}, 5]       #- []            == [true, {"b":null}, 5]
    [true, {"b":null}, 5]       #- ["0"]         == [{"b":null}, 5]
    [true, {"b":null}, 5]       #- ["b"]         == ERROR: path element at position 1 is not an integer: "b"
    [true, {"b":null}, 5]       #- ["1","b"]     == [true, {}, 5]
    [true, {"b":null}, 5]       #- ["-2","b"]    == [true, {}, 5]
    {"a": {"b":[false,4,null]}} #- ["a","b","2"] == {"a": {"b":[false,4]}}
    {"a": {"b":[false,4,null]}} #- ["a","b","c"] == ERROR: path element at position 3 is not an integer: "c"

### Other values

Expand

    1    #- {anything} == ERROR: cannot delete from scalar
    "a"  #- {anything} == ERROR: cannot delete from scalar
    true #- {anything} == ERROR: cannot delete from scalar
    null #- {anything} == ERROR: cannot delete from scalar

### PostgreSQL Documentation

Expand

        | Type   | Description                                             |  Example
    ----+--------+---------------------------------------------------------+------------------------------------
     #- | text[] | Delete the field or element with specified path         | '["a", {"b":1}]'::jsonb #- '{1,b}'
        |        | (for JSON arrays, negative integers count from the end) |

Since: 3.1.0

(||.) :: JSONBExpr a -> JSONBExpr b -> JSONBExpr c infixl 6 Source #

Requires PostgreSQL version >= 9.5

This operator concatenates two JSON values. The behaviour is self-evident when used on two arrays, but the behaviour on different combinations of JSON values might behave unexpectedly.

CAUTION: THIS FUNCTION THROWS AN EXCEPTION WHEN CONCATENATING A JSON OBJECT WITH A JSON SCALAR VALUE!

### Arrays

Expand

This operator is a standard concatenation function when used on arrays:

    [1,2]   || [2,3]   == [1,2,2,3]
    []      || [1,2,3] == [1,2,3]
    [1,2,3] || []      == [1,2,3]

### Objects

Expand

When concatenating JSON objects with other JSON objects, the fields from the JSON object on the right are added to the JSON object on the left. When concatenating a JSON object with a JSON array, the object will be inserted into the array; either on the left or right, depending on the position relative to the operator.

When concatening an object with a scalar value, an exception is thrown.

    {"a": 3.14}                    || {"b": true}         == {"a": 3.14, "b": true}
    {"a": "b"}                     || {"a": null}         == {"a": null}
    {"a": {"b": true, "c": false}} || {"a": {"b": false}} == {"a": {"b": false}}
    {"a": 3.14}                    || [1,null]            == [{"a": 3.14},1,null]
    [1,null]                       || {"a": 3.14}         == [1,null,{"a": 3.14}]
    1                              || {"a": 3.14}         == ERROR: invalid concatenation of jsonb objects
    {"a": 3.14}                    || false               == ERROR: invalid concatenation of jsonb objects

### Scalar values

Expand

Scalar values can be thought of as being singleton arrays when used with this operator. This rule does not apply when concatenating with JSON objects.

    1          || null       == [1,null]
    true       || "a"        == [true,"a"]
    [1,2]      || false      == [1,2,false]
    null       || [1,"a"]    == [null,1,"a"]
    {"a":3.14} || true       == ERROR: invalid concatenation of jsonb objects
    3.14       || {"a":3.14} == ERROR: invalid concatenation of jsonb objects
    {"a":3.14} || [true]     == [{"a":3.14},true]
    [false]    || {"a":3.14} == [false,{"a":3.14}]

### PostgreSQL Documentation

Expand

        | Type  | Description                                         |  Example
    ----+-------+-----------------------------------------------------+--------------------------------------------
     || | jsonb | Concatenate two jsonb values into a new jsonb value | '["a", "b"]'::jsonb || '["c", "d"]'::jsonb

Note: The `||` operator concatenates the elements at the top level of each of its operands. It does not operate recursively.

For example, if both operands are objects with a common key field name, the value of the field in the result will just be the value from the right hand operand.

Since: 3.1.0