Squeal.PostgreSQL.Expression.Json

json and jsonb functions and operators

Json and Jsonb Operators

(.->) :: (json `In` PGJsonType, key `In` PGJsonKey) => Operator (null json) (null key) ('Null json) infixl 8

Get JSON value (object field or array element) at a key.

(.->>) :: (json `In` PGJsonType, key `In` PGJsonKey) => Operator (null json) (null key) ('Null 'PGtext) infixl 8

Get JSON value (object field or array element) at a key, as text.

(.#>) :: json `In` PGJsonType => Operator (null json) (null ('PGvararray ('NotNull 'PGtext))) ('Null json) infixl 8

Get JSON value at a specified path.

(.#>>) :: json `In` PGJsonType => Operator (null json) (null ('PGvararray ('NotNull 'PGtext))) ('Null 'PGtext) infixl 8

Get JSON value at a specified path as text.

Jsonb Operators

(.?) :: Operator (null 'PGjsonb) (null 'PGtext) ('Null 'PGbool) infixl 9

Does the string exist as a top-level key within the JSON value?

(.?|) :: Operator (null 'PGjsonb) (null ('PGvararray ('NotNull 'PGtext))) ('Null 'PGbool) infixl 9

Do any of these array strings exist as top-level keys?

(.?&) :: Operator (null 'PGjsonb) (null ('PGvararray ('NotNull 'PGtext))) ('Null 'PGbool) infixl 9

Do all of these array strings exist as top-level keys?

(.-.) :: key `In` '['PGtext, 'PGvararray ('NotNull 'PGtext), 'PGint4, 'PGint2] => Operator (null 'PGjsonb) (null key) (null 'PGjsonb) infixl 6

Delete a key or keys from a JSON object, or remove an array element.

If the right operand is

text : Delete key / value pair or string element from left operand. Key / value pairs are matched based on their key value,

text[] : Delete multiple key / value pairs or string elements from left operand. Key / value pairs are matched based on their key value,

integer : Delete the array element with specified index (Negative integers count from the end). Throws an error if top level container is not an array.

(#-.) :: Operator (null 'PGjsonb) (null ('PGvararray ('NotNull 'PGtext))) (null 'PGjsonb) infixl 6

Delete the field or element with specified path (for JSON arrays, negative integers count from the end)

Json and Jsonb Functions

toJson :: null ty --> null 'PGjson

Returns the value as json. Arrays and composites are converted (recursively) to arrays and objects; otherwise, if there is a cast from the type to json, the cast function will be used to perform the conversion; otherwise, a scalar value is produced. For any scalar type other than a number, a Boolean, or a null value, the text representation will be used, in such a fashion that it is a valid json value.

toJsonb :: null ty --> null 'PGjsonb

Returns the value as jsonb. Arrays and composites are converted (recursively) to arrays and objects; otherwise, if there is a cast from the type to json, the cast function will be used to perform the conversion; otherwise, a scalar value is produced. For any scalar type other than a number, a Boolean, or a null value, the text representation will be used, in such a fashion that it is a valid jsonb value.

arrayToJson :: null ('PGvararray ty) --> null 'PGjson

Returns the array as a JSON array. A PostgreSQL multidimensional array becomes a JSON array of arrays.

rowToJson :: null ('PGcomposite ty) --> null 'PGjson

Returns the row as a JSON object.

jsonBuildArray :: SListI tuple => tuple ---> null 'PGjson

Builds a possibly-heterogeneously-typed JSON array out of a variadic argument list.

jsonbBuildArray :: SListI tuple => tuple ---> null 'PGjsonb

Builds a possibly-heterogeneously-typed (binary) JSON array out of a variadic argument list.

class SListI tys => JsonBuildObject tys where

Builds a possibly-heterogeneously-typed JSON object out of a variadic argument list. The elements of the argument list must alternate between text and values.

Minimal complete definition

Nothing

Methods

jsonBuildObject :: tys ---> null 'PGjson

jsonbBuildObject :: tys ---> null 'PGjsonb

Instances

JsonBuildObject ('[] :: [NullType])

Defined in Squeal.PostgreSQL.Expression.Json

Methods

jsonBuildObject :: forall (null :: PGType -> NullType). '[] ---> null 'PGjson

jsonbBuildObject :: forall (null :: PGType -> NullType). '[] ---> null 'PGjsonb

(JsonBuildObject tys, In key PGJsonKey) => JsonBuildObject ('NotNull key ': (value ': tys))

Defined in Squeal.PostgreSQL.Expression.Json

Methods

jsonBuildObject :: forall (null :: PGType -> NullType). ('NotNull key ': (value ': tys)) ---> null 'PGjson

jsonbBuildObject :: forall (null :: PGType -> NullType). ('NotNull key ': (value ': tys)) ---> null 'PGjsonb

jsonObject :: null ('PGfixarray '[n, 2] ('NotNull 'PGtext)) --> null 'PGjson

Builds a JSON object out of a text array. The array must have two dimensions such that each inner array has exactly two elements, which are taken as a key/value pair.

jsonbObject :: null ('PGfixarray '[n, 2] ('NotNull 'PGtext)) --> null 'PGjsonb

Builds a binary JSON object out of a text array. The array must have two dimensions such that each inner array has exactly two elements, which are taken as a key/value pair.

jsonZipObject :: '[null ('PGvararray ('NotNull 'PGtext)), null ('PGvararray ('NotNull 'PGtext))] ---> null 'PGjson

This is an alternate form of jsonObject that takes two arrays; one for keys and one for values, that are zipped pairwise to create a JSON object.

jsonbZipObject :: '[null ('PGvararray ('NotNull 'PGtext)), null ('PGvararray ('NotNull 'PGtext))] ---> null 'PGjsonb

This is an alternate form of jsonbObject that takes two arrays; one for keys and one for values, that are zipped pairwise to create a binary JSON object.

jsonArrayLength :: null 'PGjson --> null 'PGint4

Returns the number of elements in the outermost JSON array.

jsonbArrayLength :: null 'PGjsonb --> null 'PGint4

Returns the number of elements in the outermost binary JSON array.

jsonTypeof :: null 'PGjson --> null 'PGtext

Returns the type of the outermost JSON value as a text string. Possible types are object, array, string, number, boolean, and null.

jsonbTypeof :: null 'PGjsonb --> null 'PGtext

Returns the type of the outermost binary JSON value as a text string. Possible types are object, array, string, number, boolean, and null.

jsonStripNulls :: null 'PGjson --> null 'PGjson

Returns its argument with all object fields that have null values omitted. Other null values are untouched.

jsonbStripNulls :: null 'PGjsonb --> null 'PGjsonb

Returns its argument with all object fields that have null values omitted. Other null values are untouched.

jsonbSet :: '[null 'PGjsonb, null ('PGvararray ('NotNull 'PGtext)), null 'PGjsonb, null 'PGbool] ---> null 'PGjsonb

 jsonbSet target path new_value create_missing

Returns target with the section designated by path replaced by new_value, or with new_value added if create_missing is true and the item designated by path does not exist. As with the path orientated operators, negative integers that appear in path count from the end of JSON arrays.

jsonbInsert :: '[null 'PGjsonb, null ('PGvararray ('NotNull 'PGtext)), null 'PGjsonb, null 'PGbool] ---> null 'PGjsonb

 jsonbInsert target path new_value insert_after

Returns target with new_value inserted. If target section designated by path is in a JSONB array, new_value will be inserted before target or after if insert_after is true. If target section designated by path is in JSONB object, new_value will be inserted only if target does not exist. As with the path orientated operators, negative integers that appear in path count from the end of JSON arrays.

jsonbPretty :: null 'PGjsonb --> null 'PGtext

Returns its argument as indented JSON text.

Json and Jsonb Set Functions

jsonEach :: null 'PGjson -|-> ("json_each" ::: '["key" ::: 'NotNull 'PGtext, "value" ::: 'NotNull 'PGjson])

Expands the outermost JSON object into a set of key/value pairs.

>>> printSQL (select Star (from (jsonEach (inline (Json (object ["a" .= "foo"]))))))
SELECT * FROM json_each(('{"a":"foo"}' :: json))

jsonbEach :: null 'PGjsonb -|-> ("jsonb_each" ::: '["key" ::: 'NotNull 'PGtext, "value" ::: 'NotNull 'PGjson])

Expands the outermost binary JSON object into a set of key/value pairs.

>>> printSQL (select Star (from (jsonbEach (inline (Jsonb (object ["a" .= "foo"]))))))
SELECT * FROM jsonb_each(('{"a":"foo"}' :: jsonb))

jsonEachText :: null 'PGjson -|-> ("json_each_text" ::: '["key" ::: 'NotNull 'PGtext, "value" ::: 'NotNull 'PGtext])

Expands the outermost JSON object into a set of key/value pairs.

>>> printSQL (select Star (from (jsonEachText (inline (Json (object ["a" .= "foo"]))))))
SELECT * FROM json_each_text(('{"a":"foo"}' :: json))

jsonArrayElementsText :: null 'PGjson -|-> ("json_array_elements_text" ::: '["value" ::: 'NotNull 'PGtext])

Returns a set of text values from a JSON array

>>> printSQL (select Star (from (jsonArrayElementsText (inline (Json (toJSON ["monkey", "pony", "bear"] ))))))
SELECT * FROM json_array_elements_text(('["monkey","pony","bear"]' :: json))

jsonbEachText :: null 'PGjsonb -|-> ("jsonb_each_text" ::: '["key" ::: 'NotNull 'PGtext, "value" ::: 'NotNull 'PGtext])

Expands the outermost binary JSON object into a set of key/value pairs.

>>> printSQL (select Star (from (jsonbEachText (inline (Jsonb (object ["a" .= "foo"]))))))
SELECT * FROM jsonb_each_text(('{"a":"foo"}' :: jsonb))

jsonbArrayElementsText :: null 'PGjsonb -|-> ("jsonb_array_elements_text" ::: '["value" ::: 'NotNull 'PGtext])

Returns a set of text values from a binary JSON array

>>> printSQL (select Star (from (jsonbArrayElementsText (inline (Jsonb (toJSON ["red", "green", "cyan"] ))))))
SELECT * FROM jsonb_array_elements_text(('["red","green","cyan"]' :: jsonb))

jsonObjectKeys :: null 'PGjson -|-> ("json_object_keys" ::: '["json_object_keys" ::: 'NotNull 'PGtext])

Returns set of keys in the outermost JSON object.

>>> printSQL (jsonObjectKeys (inline (Json (object ["a" .= "foo"]))))
json_object_keys(('{"a":"foo"}' :: json))

jsonbObjectKeys :: null 'PGjsonb -|-> ("jsonb_object_keys" ::: '["jsonb_object_keys" ::: 'NotNull 'PGtext])

Returns set of keys in the outermost JSON object.

>>> printSQL (jsonbObjectKeys (inline (Jsonb (object ["a" .= "foo"]))))
jsonb_object_keys(('{"a":"foo"}' :: jsonb))

type JsonPopulateFunction fun json

Arguments

 = forall db row lat with params. json `In` PGJsonType

=> TypeExpression db ('NotNull ('PGcomposite row))

row type

-> Expression 'Ungrouped lat with db params '[] ('NotNull json)

json type

-> FromClause lat with db params '[fun ::: row]

Build rows from Json types.

jsonPopulateRecord :: JsonPopulateFunction "json_populate_record" 'PGjson

Expands the JSON expression to a row whose columns match the record type defined by the given table.

jsonbPopulateRecord :: JsonPopulateFunction "jsonb_populate_record" 'PGjsonb

Expands the binary JSON expression to a row whose columns match the record type defined by the given table.

jsonPopulateRecordSet :: JsonPopulateFunction "json_populate_record_set" 'PGjson

Expands the outermost array of objects in the given JSON expression to a set of rows whose columns match the record type defined by the given table.

jsonbPopulateRecordSet :: JsonPopulateFunction "jsonb_populate_record_set" 'PGjsonb

Expands the outermost array of objects in the given binary JSON expression to a set of rows whose columns match the record type defined by the given table.

type JsonToRecordFunction json

Arguments

 = forall lat with db params tab row. (SListI row, json `In` PGJsonType)

=> Expression 'Ungrouped lat with db params '[] ('NotNull json)

json type

-> Aliased (NP (Aliased (TypeExpression db))) (tab ::: row)

row type

-> FromClause lat with db params '[tab ::: row]

Build rows from Json types.

jsonToRecord :: JsonToRecordFunction 'PGjson

Builds an arbitrary record from a JSON object.

jsonbToRecord :: JsonToRecordFunction 'PGjsonb

Builds an arbitrary record from a binary JSON object.

jsonToRecordSet :: JsonToRecordFunction 'PGjson

Builds an arbitrary set of records from a JSON array of objects.

jsonbToRecordSet :: JsonToRecordFunction 'PGjsonb

Builds an arbitrary set of records from a binary JSON array of objects.