PostgreSQLLa base de données la plus sophistiquée au monde.

Version anglaise

9.15. JSON Functions, Operators, and Expressions

The functions, operators, and expressions described in this section operate on JSON data:

To learn more about the SQL/JSON standard, see [sqltr-19075-6]. For details on JSON types supported in PostgreSQL™, see Section 8.14, « Types JSON ».

9.15.1. SQL/JSON Path Expressions

SQL/JSON path expressions specify the items to be retrieved from the JSON data, similar to XPath expressions used for SQL access to XML. In PostgreSQL™, path expressions are implemented as the jsonpath data type, described in Section 8.14.6, « jsonpath Type ».

JSON query functions and operators pass the provided path expression to the path engine for evaluation. If the expression matches the JSON data to be queried, the corresponding SQL/JSON item is returned. Path expressions are written in the SQL/JSON path language and can also include arithmetic expressions and functions. Query functions treat the provided expression as a text string, so it must be enclosed in single quotes.

A path expression consists of a sequence of elements allowed by the jsonpath data type. The path expression is evaluated from left to right, but you can use parentheses to change the order of operations. If the evaluation is successful, a sequence of SQL/JSON items (SQL/JSON sequence) is produced, and the evaluation result is returned to the JSON query function that completes the specified computation.

To refer to the JSON data to be queried (the context item), use the $ sign in the path expression. It can be followed by one or more accessor operators, which go down the JSON structure level by level to retrieve the content of context item. Each operator that follows deals with the result of the previous evaluation step.

For example, suppose you have some JSON data from a GPS tracker that you would like to parse, such as:

{ "track" :
  {
    "segments" : [ 
      { "location":   [ 47.763, 13.4034 ],
        "start time": "2018-10-14 10:05:14",
        "HR": 73
      },
      { "location":   [ 47.706, 13.2635 ],
        "start time": "2018-10-14 10:39:21",
        "HR": 130
      } ]
  }
}

To retrieve the available track segments, you need to use the .key accessor operator for all the preceding JSON objects:

'$.track.segments'

If the item to retrieve is an element of an array, you have to unnest this array using the [*] operator. For example, the following path will return location coordinates for all the available track segments:

'$.track.segments[*].location'

To return the coordinates of the first segment only, you can specify the corresponding subscript in the [] accessor operator. Note that the SQL/JSON arrays are 0-relative:

'$.track.segments[0].location'

The result of each path evaluation step can be processed by one or more jsonpath operators and methods listed in Section 9.15.1.2, « SQL/JSON Path Operators and Methods ». Each method must be preceded by a dot, while arithmetic and boolean operators are separated from the operands by spaces. For example, you can get an array size:

'$.track.segments.size()'

For more examples of using jsonpath operators and methods within path expressions, see Section 9.15.1.2, « SQL/JSON Path Operators and Methods ».

When defining the path, you can also use one or more filter expressions, which work similar to the WHERE clause in SQL. Each filter expression can provide one or more filtering conditions that are applied to the result of the path evaluation. Each filter expression must be enclosed in parentheses and preceded by a question mark. Filter expressions are evaluated from left to right and can be nested. The @ variable denotes the current path evaluation result to be filtered, and can be followed by one or more accessor operators to define the JSON element by which to filter the result. Functions and operators that can be used in the filtering condition are listed in Tableau 9.45, « jsonpath Filter Expression Elements ». SQL/JSON defines three-valued logic, so the result of the filter expression may be true, false, or unknown. The unknown value plays the same role as SQL NULL. Further path evaluation steps use only those items for which filter expressions return true.

Suppose you would like to retrieve all heart rate values higher than 130. You can achieve this using the following expression:

'$.track.segments[*].HR ? (@ > 130)'

To get the start time of segments with such values instead, you have to filter out irrelevant segments before returning the start time, so the filter is applied to the previous step and the path in the filtering condition is different:

'$.track.segments[*] ? (@.HR > 130)."start time"'

You can use several filter expressions on the same nesting level, if required. For example, the following expression selects all segments that contain locations with relevant coordinates and high heart rate values:

'$.track.segments[*] ? (@.location[1] < 13.4) ? (@.HR > 130)."start time"'

Using filter expressions at different nesting levels is also allowed. The following example first filters all segments by location, and then returns high heart rate values for these segments, if available:

'$.track.segments[*] ? (@.location[1] < 13.4).HR ? (@ > 130)'

You can also nest filters within each other:

'$.track ? (@.segments[*] ? (@.HR > 130)).segments.size()'

This expression returns the size of the track if it contains any segments with high heart rate values, or an empty sequence otherwise.

PostgreSQL™'s implementation of SQL/JSON path language has the following deviations from the SQL/JSON standard:

  • .datetime() item method is not implemented yet mainly because immutable jsonpath functions and operators cannot reference session timezone, which is used in some datetime operations. Datetime support will be added to jsonpath in future versions of PostgreSQL™.

  • A path expression can be a boolean predicate, although the SQL/JSON standard allows predicates only in filters. This is necessary for implementation of the @@ operator. For example, the followingjsonpath expression is valid in PostgreSQL™:

    '$.track.segments[*].HR < 70'
    

9.15.1.1. Strict and Lax Modes

When you query JSON data, the path expression may not match the actual JSON data structure. An attempt to access a non-existent member of an object or element of an array results in a structural error. SQL/JSON path expressions have two modes of handling structural errors:

  • lax (default) -- the path engine implicitly adapts the queried data to the specified path. Any remaining structural errors are suppressed and converted to empty SQL/JSON sequences.

  • strict -- if a structural error occurs, an error is raised.

The lax mode facilitates matching of a JSON document structure and path expression if the JSON data does not conform to the expected schema. If an operand does not match the requirements of a particular operation, it can be automatically wrapped as an SQL/JSON array or unwrapped by converting its elements into an SQL/JSON sequence before performing this operation. Besides, comparison operators automatically unwrap their operands in the lax mode, so you can compare SQL/JSON arrays out-of-the-box. An array of size 1 is considered equal to its sole element. Automatic unwrapping is not performed only when:

  • The path expression contains type() or size() methods that return the type and the number of elements in the array, respectively.

  • The queried JSON data contain nested arrays. In this case, only the outermost array is unwrapped, while all the inner arrays remain unchanged. Thus, implicit unwrapping can only go one level down within each path evaluation step.

For example, when querying the GPS data listed above, you can abstract from the fact that it stores an array of segments when using the lax mode:

'lax $.track.segments.location'

In the strict mode, the specified path must exactly match the structure of the queried JSON document to return an SQL/JSON item, so using this path expression will cause an error. To get the same result as in the lax mode, you have to explicitly unwrap the segments array:

'strict $.track.segments[*].location'

9.15.1.2. SQL/JSON Path Operators and Methods

Tableau 9.44. jsonpath Operators and Methods

Operator/Method Description Example JSON Example Query Result
+ (unary) Plus operator that iterates over the SQL/JSON sequence {"x": [2.85, -14.7, -9.4]} + $.x.floor() 2, -15, -10
- (unary) Minus operator that iterates over the SQL/JSON sequence {"x": [2.85, -14.7, -9.4]} - $.x.floor() -2, 15, 10
+ (binary) Addition [2] 2 + $[0] 4
- (binary) Subtraction [2] 4 - $[0] 2
* Multiplication [4] 2 * $[0] 8
/ Division [8] $[0] / 2 4
% Modulus [32] $[0] % 10 2
type() Type of the SQL/JSON item [1, "2", {}] $[*].type() "number", "string", "object"
size() Size of the SQL/JSON item {"m": [11, 15]} $.m.size() 2
double() Approximate numeric value converted from a string {"len": "1.9"} $.len.double() * 2 3.8
ceiling() Nearest integer greater than or equal to the SQL/JSON number {"h": 1.3} $.h.ceiling() 2
floor() Nearest integer less than or equal to the SQL/JSON number {"h": 1.3} $.h.floor() 1
abs() Absolute value of the SQL/JSON number {"z": -0.3} $.z.abs() 0.3
keyvalue() Sequence of object's key-value pairs represented as array of objects containing three fields ("key", "value", and "id"). "id" is an unique identifier of the object key-value pair belongs to. {"x": "20", "y": 32} $.keyvalue() {"key": "x", "value": "20", "id": 0}, {"key": "y", "value": 32, "id": 0}

Tableau 9.45. jsonpath Filter Expression Elements

Value/Predicate Description Example JSON Example Query Result
== Equality operator [1, 2, 1, 3] $[*] ? (@ == 1) 1, 1
!= Non-equality operator [1, 2, 1, 3] $[*] ? (@ != 1) 2, 3
<> Non-equality operator (same as !=) [1, 2, 1, 3] $[*] ? (@ <> 1) 2, 3
< Less-than operator [1, 2, 3] $[*] ? (@ < 2) 1, 2
<= Less-than-or-equal-to operator [1, 2, 3] $[*] ? (@ < 2) 1
> Greater-than operator [1, 2, 3] $[*] ? (@ > 2) 3
> Greater-than-or-equal-to operator [1, 2, 3] $[*] ? (@ >= 2) 2, 3
true Value used to perform comparison with JSON true literal [{"name": "John", "parent": false}, {"name": "Chris", "parent": true}] $[*] ? (@.parent == true) {"name": "Chris", "parent": true}
false Value used to perform comparison with JSON false literal [{"name": "John", "parent": false}, {"name": "Chris", "parent": true}] $[*] ? (@.parent == false) {"name": "John", "parent": false}
null Value used to perform comparison with JSON null value [{"name": "Mary", "job": null}, {"name": "Michael", "job": "driver"}] $[*] ? (@.job == null) .name "Mary"
&& Boolean AND [1, 3, 7] $[*] ? (@ > 1 && @ < 5) 3
|| Boolean OR [1, 3, 7] $[*] ? (@ < 1 || @ > 5) 7
! Boolean NOT [1, 3, 7] $[*] ? (!(@ < 5)) 7
like_regex Tests pattern matching with POSIX regular expressions ["abc", "abd", "aBdC", "abdacb", "babc"] $[*] ? (@ like_regex "^ab.*c" flag "i") "abc", "aBdC", "abdacb"
starts with Tests whether the second operand is an initial substring of the first operand ["John Smith", "Mary Stone", "Bob Johnson"] $[*] ? (@ starts with "John") "John Smith"
exists Tests whether a path expression has at least one SQL/JSON item {"x": [1, 2], "y": [2, 4]} strict $.* ? (exists (@ ? (@[*] > 2))) 2, 4
is unknown Tests whether a boolean condition is unknown [-1, 2, 7, "infinity"] $[*] ? ((@ > 0) is unknown) "infinity"

9.15.2. JSON Functions and Operators

Tableau 9.46, « json and jsonb Operators » shows the operators that are available for use with JSON data types (see Section 8.14, « Types JSON »).

Tableau 9.46. json and jsonb Operators

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

[Note]

Note

There are parallel variants of these operators for both the json and jsonb types. The field/element/path extraction operators return the same type as their left-hand input (either json or jsonb), except for those specified as returning text, which coerce the value to text. The field/element/path extraction operators return NULL, rather than failing, if the JSON input does not have the right structure to match the request; for example if no such element exists. The field/element/path extraction operators that accept integer JSON array subscripts all support negative subscripting from the end of arrays.

The standard comparison operators shown in Tableau 9.1, « Opérateurs de comparaison » are available for jsonb, but not for json. They follow the ordering rules for B-tree operations outlined at Section 8.14.4, « Indexation jsonb ».

Some further operators also exist only for jsonb, as shown in Tableau 9.47, « Additional jsonb Operators ». Many of these operators can be indexed by jsonb operator classes. For a full description of jsonb containment and existence semantics, see Section 8.14.3, « Existence et inclusion jsonb ». Section 8.14.4, « Indexation jsonb » describes how these operators can be used to effectively index jsonb data.

Tableau 9.47. Additional jsonb Operators

Operator Right Operand Type Description Example
@> jsonb Est-ce que la valeur JSON contient au premier niveau les entrées clefs/valeurs de la valeur JSON à sa droite ? '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb
<@ jsonb Les entrées clefs/valeurs de la valeur JSON sont-elles contenues au premier niveau de la valeur JSON de droite ? '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb
? text Est-ce que la chaîne existe comme clef de premier niveau dans la valeur JSON ? '{"a":1, "b":2}'::jsonb ? 'b'
?| text[] Est-ce qu'une au moins des chaînes contenues dans le tableau existe comme clef de premier niveau ? '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c']
?& text[] Est-ce que toutes les chaînes du tableau existent comme clef de premier niveau ? '["a", "b"]'::jsonb ?& array['a', 'b']
|| jsonb Effectue la concaténation de deux valeurs de type jsonb dans une nouvelle valeur jsonb '["a", "b"]'::jsonb || '["c", "d"]'::jsonb
- text Supprime la paire clef/valeur ou l'élément de type chaîne de l'opérande de gauche. Les paires clefs/valeurs sont sélectionnées selon la valeur de leur clef. '{"a": "b"}'::jsonb - 'a'
- text[] Supprime plusieurs paires de clé/valeur ou d'éléments string de l'opérande de gauche. La correspondance des paires de clé/valeur est faite en fonction de la valeur de leur clé. '{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[]
- integer Supprime l'élément du tableau ayant l'index indiqué (les nombres négatifs décomptent à partir de la fin du tableau). Lève une erreur si le conteneur de premier niveau n'est pas un tableau '["a", "b"]'::jsonb - 1
#- text[] Supprime le champ ou l'élément ayant le chemin indiqué (pour les tableaux JSON, les chiffres négatifs décomptent à partir de la fin) '["a", {"b":1}]'::jsonb #- '{1,b}'
@? jsonpath Does JSON path returns any item for the specified JSON value? '{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ > 2)'
@@ jsonpath JSON path predicate check result for the specified JSON value. Only first result item is taken into account. If there is no results or first result item is not bool, then NULL is returned. '{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 2'

[Note]

Note

L'opérateur || concatène les éléments situés au premier niveau de chacun de ses opérandes. Il n'opère pas récursivement. Par exemple, si les deux opérandes sont des objets avec une clef commune, la valeur du champ dans le résultat sera uniquement celle de l'opérande de droite.

[Note]

Note

The @? and @@ operators suppress errors including: lacking object field or array element, unexpected JSON item type and numeric errors. This behavior might be helpful while searching over JSON document collections of varying structure.

Tableau 9.48, « Fonctions de création de données JSON » montre les fonctions disponibles pour la création de valeurs json and jsonb values. (Il n'y a pas de fonctions équivalentes pour le type jsonb des fonctions row_to_json et array_to_json. Cependant, la fonction to_jsonb fournit la plupart des fonctionnalités que ces fonctions fourniraient.)

Tableau 9.48. Fonctions de création de données JSON

Fonction Description Exemple Exemple du résultat

to_json(anyelement)

to_jsonb(anyelement)

Renvoie la valeur en tant que type json ou jsonb. Les tableaux et valeurs composites sont convertis (récursivement) en tableaux et objets. Dans le cas contraire, s'il existe une conversion de ce type vers le type json, la fonction de conversion sera utilisée pour réaliser la conversion. Dans les autres cas, une valeur scalaire est produite. Pour tout type scalaire autre qu'un nombre, un booléen ou une valeur NULL, la représentation textuelle sera utilisée, de telle manière que cela soit une valeur valide pour les types json ou jsonb. to_json('Fred said "Hi."'::text) "Fred said \"Hi.\""
array_to_json(anyarray [, pretty_bool]) Renvoie le tableau sous la forme d'un tableau JSON. Un tableau PostgreSQL multidimensionnel devient un tableau JSON de tableaux. Des retours à la ligne seront ajoutés entre les éléments de la première dimension si pretty_bool vaut true. array_to_json('{{1,5},{99,100}}'::int[]) [[1,5],[99,100]]
row_to_json(record [, pretty_bool]) Renvoie la ligne sous la forme d'un objet JSON. Des retours à la ligne seront ajoutés entre les éléments du niveau 1 si pretty_bool vaut true. row_to_json(row(1,'foo')) {"f1":1,"f2":"foo"}

json_build_array(VARIADIC "any")

jsonb_build_array(VARIADIC "any")

Construit un tableau JSON de type possiblement hétérogène à partir d'une liste d'arguments variables. json_build_array(1,2,'3',4,5) [1, 2, "3", 4, 5]
json_build_object(VARIADIC "any") Construit un objet JSON à partir d'une liste d'arguments variables. Par convention, la liste d'arguments consiste en des clés et valeurs en alternance. json_build_object('foo',1,'bar',2) {"foo": 1, "bar": 2}

json_object(text[])

jsonb_object(text[])

Construit un objet JSON à partir d'un tableau de textes. Le tableau doit avoir soit exactement une dimension avec un nombre pair de membres, auquel cas ils sont pros comme des paires clé/valeur en alternance, soit deux dimensions, de telle façon que chaque tableau interne contienne exactement deux éléments, qui sont pris sous la forme d'une paire clé/valeur.

json_object('{a, 1, b, "def", c, 3.5}')

json_object('{{a, 1},{b, "def"},{c, 3.5}}')

{"a": "1", "b": "def", "c": "3.5"}

json_object(keys text[], values text[])

jsonb_object(keys text[], values text[])

Cette forme de json_object prend des clés et valeurs sous forme de paires à partir de deux tableaux séparés. Tous les autres aspects sont identiques à la fonction avec un seul argument. json_object('{a, b}', '{1,2}') {"a": "1", "b": "2"}

[Note]

Note

array_to_json et row_to_json ont le même comportement que to_json, en dehors du fait qu'elles ne proposent pas d'option d'affichage propre. Le comportement décrit pour to_json s'applique à chaque valeur individuelle convertie par les autres fonctions de création JSON.

[Note]

Note

L'extension hstore dispose d'une conversion du type hstore vers le type json, pour que les valeurs hstore converties via les fonctions de création JSON soient représentées en tant qu'objets JSON et non pas en tant que les valeurs des chaînes de caractères habituelles.

Tableau 9.49, « Fonctions de traitement du JSON » montre les fonctions disponibles pour le traitement des valeurs json et jsonb.

Tableau 9.49. Fonctions de traitement du JSON

Fonction Type renvoyé Description Exemple Exemple de résultat

json_array_length(json)

jsonb_array_length(jsonb)

int Renvoie le nombre d'éléments dans le tableau JSON externe. json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]') 5

json_each(json)

jsonb_each(jsonb)

setof key text, value json

setof key text, value jsonb

Étend l'objet JSON extérieur en un ensemble de paires clé/valeur. select * from json_each('{"a":"foo", "b":"bar"}')
 key | value
-----+-------
 a   | "foo"
 b   | "bar"
                

json_each_text(json)

jsonb_each_text(jsonb)

setof key text, value text Étend l'objet JSON externe en un ensemble de paires clé/valeur. La valeur renvoyée est de type text. select * from json_each_text('{"a":"foo", "b":"bar"}')
 key | value
-----+-------
 a   | foo
 b   | bar
                

json_extract_path(from_json json, VARIADIC path_elems text[])

jsonb_extract_path(from_json jsonb, VARIADIC path_elems text[])

json

jsonb

Renvoie l'objet JSON pointé par path_elems (équivalent à l'opérateur #>). json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4') {"f5":99,"f6":"foo"}

json_extract_path_text(from_json json, VARIADIC path_elems text[])

jsonb_extract_path_text(from_json jsonb, VARIADIC path_elems text[])

text Renvoie l'objet JSON pointé par path_elems as text (équivalent à l'opérateur #>>). json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6') foo

json_object_keys(json)

jsonb_object_keys(jsonb)

setof text Renvoie l'ensemble de clés de l'objet externe JSON. json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}')
 json_object_keys
------------------
 f1
 f2
                

json_populate_record(base anyelement, from_json json)

jsonb_populate_record(base anyelement, from_json jsonb)

anyelement Étend l'objet dans from_json en une ligne dont les colonnes correspondent au type d'enregistrement défini par base (voir la note ci-dessous). select * from json_populate_record(null::myrowtype, '{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "a b c"}}')
 a |   b       |      c
---+-----------+-------------
 1 | {2,"a b"} | (4,"a b c")
                

json_populate_recordset(base anyelement, from_json json)

jsonb_populate_recordset(base anyelement, from_json jsonb)

setof anyelement Étend le tableau externe d'objets dans from_json en un ensemble de lignes dont les colonnes correspondent au type d'enregistrement défini par base (voir la note ci-dessous). select * from json_populate_recordset(null::myrowtype, '[{"a":1,"b":2},{"a":3,"b":4}]')
 a | b
---+---
 1 | 2
 3 | 4
                

json_array_elements(json)

jsonb_array_elements(jsonb)

setof json

setof jsonb

Étend un tableau JSON en un ensemble de valeurs JSON. select * from json_array_elements('[1,true, [2,false]]')
   value
-----------
 1
 true
 [2,false]
                

json_array_elements_text(json)

jsonb_array_elements_text(jsonb)

setof text Étend un tableau JSON en un ensemble de valeurs text. select * from json_array_elements_text('["foo", "bar"]')
   value
-----------
 foo
 bar
                

json_typeof(json)

jsonb_typeof(jsonb)

text Renvoie le type de la valeur externe du JSON en tant que chaîne de type text. Les types possibles sont object, array, string, number, boolean et null. json_typeof('-123.4') number

json_to_record(json)

jsonb_to_record(jsonb)

record Construit un enregistrement arbitraire à partir d'un objet JSON (voir la note ci-dessous). Comme avec toutes les fonctions renvoyant le type record, l'appelant doit définir explicitement la structure du type record avec une clause AS. select * from json_to_record('{"a":1,"b":[1,2,3],"c":[1,2,3],"e":"bar","r": {"a": 123, "b": "a b c"}}') as x(a int, b text, c int[], d text, r myrowtype)
 a |    b    |    c    | d |       r
---+---------+---------+---+---------------
 1 | [1,2,3] | {1,2,3} |   | (123,"a b c")
                

json_to_recordset(json)

jsonb_to_recordset(jsonb)

setof record Construit un ensemble arbitraire d'enregistrements à partir d'un tableau JSON d'objets (voir la note ci-dessous). Comme avec toutes les fonctions renvoyant le type record, l'appelant doit définir explicitement la structure du type record avec une clause AS. select * from json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]') as x(a int, b text);
 a |  b
---+-----
 1 | foo
 2 |
                

json_strip_nulls(from_json json)

jsonb_strip_nulls(from_json jsonb)

json

jsonb

Renvoie from_json en omettant tous les champs des objets qui ont des valeurs NULL. Les autres valeurs NULL ne sont pas omises. json_strip_nulls('[{"f1":1,"f2":null},2,null,3]') [{"f1":1},2,null,3]

jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])

jsonb

Renvoie target avec la section dont le chemin est désigné par path remplacée par new_value, ou avec new_value ajoutée si create_missing est true (ce qui est la valeur par défaut) et l'élément désigné par le chemin path n'existe pas. De la même manière qu'avec les opérateurs désignant des chemins, les nombres négatifs qui apparaissent dans path décomptent à partir de la fin des tableaux JSON.

jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false)

jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]')

[{"f1":[2,3,4],"f2":null},2,null,3]

[{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]

jsonb_insert(target jsonb, path text[], new_value jsonb, [insert_after boolean])

jsonb

Renvoie target avec new_value insérée. Si la section target désignée par path est dans un tableau JSONB, new_value sera insérée avant la cible ou après la cible si insert_after vaut true (la valeur par défaut est false). Si la section target désignée par path est dans un objet JSONB, new_value sera insérée seulement si target n'existe pas. Tout comme avec les opérateurs orientés chemin, les entiers négatifs qui apparaissent dans path sont décomptés à partir de la fin des tableaux JSON.

jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"nouvelle_valeur"')

jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"nouvelle_valeur"', true)

{"a": [0, "nouvelle_valeur", 1, 2]}

{"a": [0, 1, "nouvelle_valeur", 2]}

jsonb_pretty(from_json jsonb)

text

Renvoie from_json comme texte JSON indenté. jsonb_pretty('[{"f1":1,"f2":null},2,null,3]')
[
    {
        "f1": 1,
        "f2": null
    },
    2,
    null,
    3
]

jsonb_path_exists(target jsonb, path jsonpath [, vars jsonb, silent bool])

boolean Checks whether JSON path returns any item for the specified JSON value.

jsonb_path_exists('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}')

true

jsonb_path_match(target jsonb, path jsonpath [, vars jsonb, silent bool])

boolean Returns JSON path predicate result for the specified JSON value. Only first result item is taken into account. If there is no results or first result item is not bool, then NULL is returned.

jsonb_path_match('{"a":[1,2,3,4,5]}', 'exists($.a[*] ? (@ >= $min && @ <= $max))', '{"min":2,"max":4}')

true

jsonb_path_query(target jsonb, path jsonpath [, vars jsonb, silent bool])

setof jsonb Gets all JSON items returned by JSON path for the specified JSON value.

select * jsonb_path_query('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}');

 jsonb_path_query
------------------
 2
 3
 4
           

jsonb_path_query_array(target jsonb, path jsonpath [, vars jsonb, silent bool])

jsonb Gets all JSON items returned by JSON path for the specified JSON value and wraps result into an array.

jsonb_path_query_array('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}')

[2, 3, 4]

jsonb_path_query_first(target jsonb, path jsonpath [, vars jsonb, silent bool])

jsonb Gets the first JSON item returned by JSON path for the specified JSON value. Returns NULL on no results.

jsonb_path_query_first('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}')

2


[Note]

Note

Un grand nombre de ces fonctions et opérateurs convertiront les échappements Unicode en chaînes JSON avec le caractère approprié. Ce n'est pas un problème si la valeur en entrée est de type jsonb parce que la conversion est déjà faite. Par contre, pour une valeur de type json, cela pourrait résulter par le renvoi d'une erreur, comme indiqué dans Section 8.14, « Types JSON ».

[Note]

Note

Alors que les exemples pour les fonctions json_populate_record, json_populate_recordset, json_to_record et json_to_recordset utilisent des constantes, l'utilisation typique serait de référencer une table dans la clause FROM ou d'utiliser une de ses colonnes json ou jsonb comme argument de la fonction. Les valeurs des clés extraites peuvent être ensuite référencées dans d'autres parties de la requête, comme les clauses WHERE et les listes cibles. Extraire plusieurs valeurs de cette façon peut améliorer les performances par rapport à une extraction séparée de chaque valeur par clé avec les opérateurs clauses et listes cibles.

Les clés JSON correspondent aux noms de colonne identiques dans le type ligne cible. La compatibilité du type JSON pour ces fonctions est du « best effort » et pourrait résulter en des valeurs non désirées pour certains types. Les champs JSON qui n'apparaissent pas dans le type ligne cible seront omis de la sortie, et les colonnes cibles qui ne correspondent à aucun champ JSON seront tout simplement NULL.

[Note]

Note

Tous les éléments du chemin du paramètre path des fonctions jsonb_set et jsonb_insert, sauf le dernier élément, doivent être présents dans la target. Si create_missing vaut false, tous les éléments du paramètre path de jsonb_set doivent être présents. Si ces conditions ne sont pas satisfaites, target est renvoyé inchangé.

Si le dernier élément d'un chemin est la clef d'un objet, il sera créé avec la nouvelle valeur si absent. Si le dernier élément d'un chemin est l'index d'un tableau, si il est positif, l'élément à positionner est trouvé en comptant à partir de la gauche. Si il est négatif, le décompte se fait à partir de la droite (par exemple, -1 désigne l'élément le plus à droite, et ainsi de suite). Si l'élément est en dehors de l'intervalle existant -longueur_tableau .. longeur_tableau - 1, et create_missing est true, la nouvelle valeur est ajoutée au début du tableau pour un élément négatif, et à la fin du tableau pour un élément positif.

[Note]

Note

La valeur de retour null de la fonction json_typeof ne doit pas être confondue avec la valeur SQL NULL. Bien qu'appeler json_typeof('null'::json) renverra null, appeler json_typeof(NULL::json) renverra un NULL au sens SQL.

[Note]

Note

Si l'argument de json_strip_nulls contient des noms de champs dupliqués dans les objets, le résultat pourrait être sémantiquement quelque peu différent, dépendant de l'ordre dans lequel ils apparaissent. Ce n'est pas un problème pour jsonb_strip_nulls, car les valeurs de type jsonb n'ont jamais des noms de champs dupliqués.

[Note]

Note

The jsonb_path_exists, jsonb_path_match, jsonb_path_query, jsonb_path_query_array and jsonb_path_query_first functions have optional vars and silent argument.

When vars argument is specified, it constitutes an object contained variables to be substituted into jsonpath expression.

When silent argument is specified and has true value, the same errors are suppressed as it is in the @? and @@ operators.

Voir aussi Section 9.20, « Fonctions d'agrégat » pour la fonction d'agrégat json_agg qui agrège les valeurs en tant que JSON, et la fonction d'agrégat json_object_agg qui agrège les paires de valeurs en un objet JSON, et leurs équivalents jsonb, jsonb_agg et jsonb_object_agg.