Tabla de contenidos
PostgreSQL proporciona dos tipos para almacenar datos JSON: json y jsonb. El tipo json almacena una copia exacta del texto de entrada, que debe volver a analizarse cada vez que se procesa. Por otro lado, el tipo jsonb almacena los datos JSON en un formato binario descompuesto que hace que su entrada sea ligeramente más lenta debido a la sobrecarga de conversión añadida, pero significativamente más rápida de procesar, ya que no es necesario volver a analizarla. El tipo jsonb también admite indexación.
En MobilityDB, los tipos textset y ttext se utilizan para representar, respectivamente, conjuntos de valores JSON y valores JSON temporales. Además, el tipo jsonb sirve como tipo base para definir los tipos jsonbset y tjsonb. La mayoría de las funciones y operadores descritos en los capítulos anteriores para los tipos conjunto y temporal también son aplicables a los tipos JSON correspondientes. Además, hay funciones específicas definidas para estos tipos, que se derivan de las funciones correspondientes de los tipos json y jsonb.
En este capítulo, describimos los tipos JSON de MobilityDB y sus operaciones asociadas. Remitimos a la documentación de PostgreSQL para una explicación detallada de los tipos json y jsonb y su funcionalidad. Hemos buscado habilitar la misma sintaxis de las funciones originales de PostgreSQL para los tipos correspondientes de MobilityDB, como se ilustra en este documento.
Considere por ejemplo el operador ->, que extrae un campo de objeto JSON con una clave dada.
SELECT jsonb '{"unit": "km", "speed": 10}' -> text 'speed';
-- 10
Aplicar el mismo operador a un conjunto JSONB y a un valor JSONB temporal produce los siguientes resultados
SELECT jsonbset '{"{\"unit\": \"km\", \"speed\": 10}",
"{\"unit\": \"km\", \"speed\": 20}"}' -> text 'speed';
-- {"10", "20"}
SELECT tjsonb '[{"unit": "km", "speed": 10}@2001-01-01,
{"unit": "km", "speed": 20}@2001-01-02]' -> text 'speed';
-- {[10@2001-01-01, 20@2001-01-02]}
que se obtienen aplicando el operador de PostgreSQL -> a cada elemento del conjunto JSONB y a cada instante del valor JSONB temporal.
Al manipular colecciones JSON de estructura variable, puede ocurrir que un elemento esté definido en algunos de los documentos pero no en todos. PostgreSQL proporciona el modo lax para este propósito, donde el argumento null_value_treatment determina el comportamiento en el caso en que una función devuelve un valor NULL. El argumento puede tomar uno de los siguientes valores: 'raise_exception', 'use_json_null', 'delete_key', 'return_target', donde 'use_json_null' es el valor por defecto. En PostgreSQL el modo lax se admite solo para operaciones de actualización (no de consulta) con la función jsonb_set_lax. En MobilityDB, hemos mantenido la misma semántica para la función correspondiente jsonbset_set_lax, pero habilitamos un comportamiento similar para todas las operaciones JSON que pueden devolver un valor nulo, salvo que reemplazamos el valor 'return_target' por 'return_null', ya que el primero no tiene sentido para operaciones sobre conjuntos. Para operadores como ->, se utiliza el valor por defecto 'use_json_null' y no se puede cambiar, mientras que para las funciones correspondientes jsonbset_object_field, el último argumento especifica el comportamiento en el caso en que la función devuelve NULL. Ilustramos este comportamiento para la función jsonbset_object_field a continuación.
Extraer un campo de objeto JSON especificado por una clave
{textset,jsonbset} -> text → {textset,jsonbset}
jsonbset ->> text → textset
jsonbset_object_field(jsonbset,text,null_handle text='use_json_null') → jsonbset
jsonbset_object_field_text(jsonbset,text,null_handle text='use_json_null') → textset
SELECT textset '{[{"unit": "km", "speed": 10},
{"unit": "km", "speed": 20}]}' -> text 'speed';
-- {["10", "20"]}
SELECT jsonbset '[{"position":"Point(1 1)", "speed":10},
{"position":"Point(2 2)", "speed":20}]' ->> text 'position';
-- ["Point(1 1)", "Point(2 2)"]
SELECT textset '{[{"unit": "km", "speed": 10},
{"unit": "km"}, {"unit": "km", "speed": 10}]}' -> text 'speed';
-- {[10, null, 10]}
Mostramos a continuación el comportamiento de la función jsonbset_object_field para los posibles valores del argumento null_handle. Todas las funciones JSON que pueden devolver un valor NULL se comportan de forma similar en MobilityDB.
SELECT jsonbset_object_field(textset '{[{"unit": "km", "speed": 10},
{"unit": "km"}, {"unit": "km", "speed": 10}]}', 'speed',
'raise_exception');
-- ERROR: The lifted operation returned NULL
SELECT jsonbset_object_field(textset '{[{"unit": "km", "speed": 10},
{"unit": "km"}, {"unit": "km", "speed": 10}]}', 'speed',
'use_json_null');
-- {[10, null, 10]}
SELECT jsonbset_object_field(textset '{[{"unit": "km", "speed": 10},
{"unit": "km"}, {"unit": "km", "speed": 10}]}', 'speed',
'delete_key');
-- {[10, 10), [10]}
SELECT jsonbset_object_field(textset '{[{"unit": "km", "speed": 10},
{"unit": "km"}, {"unit": "km", "speed": 10}]}', 'speed',
'return_null');
-- NULL
Extraer un campo de objeto JSON especificado por una ruta
{textset,jsonbset} #> text[] → {textset,jsonbset}
jsonbset_extract_path(jsonbset,text[],null_handle text='use_json_null') → jsonbset
jsonbset_extract_path_text(jsonbset,text[],null_handle text='use_json_null') → textset
SELECT textset '[{"speed": {"unit": "km", "value": 10}},
{"speed": {"unit": "km", "value": 20}}]' #> ARRAY[text 'speed', 'value'];
-- {["10", "20"]}
SELECT jsonbset '[{"position":"Point(1 1)", "PoIs":["Grand Place", "La Bourse"]},
{"position":"Point(2 2)", "PoIs":["Palais Royal", "Manneken Pis"]}]' #>>
ARRAY[text 'PoIs', '1'];
-- ["La Bourse", "Manneken Pis"]
Extraer un elemento de un arreglo JSON
jsonbset_array_element(jsonbset,integer,null_handle text='use_json_null') → jsonbset
jsonbset_array_element_text(jsonbset,integer,null_handle text='use_json_null') → jsonbset
SELECT jsonbset_array_element(textset '[["Grand Place", "La Bourse"], ["Palais Royal", "Manneken Pis"]]', 0); -- ["Grand Place", "Palais Royal"] SELECT jsonbset_array_element(jsonbset '[["Grand Place", "La Bourse"], ["Palais Royal", "Manneken Pis"]]', 1); -- ["La Bourse", "Manneken Pis"]
Extraer un valor alfanumérico de un conjunto JSONB dado por una clave
intset(jsonbset,text,null_handle text='raise_exception') → tint
floatset(jsonbset,text,null_handle text='raise_exception') → tfloat
textset(jsonbset,text,null_handle text='raise_exception') → textset
Tenga en cuenta que el valor use_json_null no puede utilizarse para las funciones anteriores y por lo tanto se utiliza el valor por defecto 'raise_exception'.
SELECT intset(jsonbset '{"{\"speed\": 10, \"units\": \"km/h\"}",
"{\"speed\": 20, \"units\": \"km/h\"}"}', 'speed');
-- {10, 20}
SELECT floatset(jsonbset '{"{\"speed\": 10, \"units\": \"km/h\"}",
"{\"speed\": 20, \"units\": \"km/h\"}", "{\"speed\": 25}"}', 'speed');
-- {10, 20, 25}
SELECT textset(jsonbset '{"{\"road\": \"Bvd Gén. Jacques\", \"category\": \"primary\"}",
"{\"road\": \"Bvd de la Cambre\", \"category\": \"residential\"}"}', 'category');
-- {primary, residential}
Concatenación JSONB temporal
jsonbset || {jsonb,jsonbset} → jsonbset
SELECT jsonbset '{[{"speed":10}, {"speed":20}]}' ||
'{"unit":"km"}'::jsonb;
-- {[{"unit": "km", "speed": 10}, {"unit": "km", "speed": 20}]}
SELECT jsonbset '{[{"speed":10}, {"speed":20}]}' ||
jsonbset '{[{"Position":"Point(1 1)"}, {"Position":"Point(2 2)"}]}';
/* {[{"speed": 10, "Position": "Point(1 1)"},
{"speed": 20, "Position": "Point(2 2)"}]} */
Eliminación JSONB temporal
jsonbset - {int,text,text[]} → jsonbset
jsonbset #- text[] → jsonbset
SELECT jsonbset '{[{"unit": "km", "speed": 10},
{"unit": "km", "speed": 20}]}' - 'unit';
-- {[{"speed": 10}, {"speed": 20}]}
SELECT jsonbset '[["Grand Place", "La Bourse"],
["Palais Royal", "Manneken Pis"]]' - 1;
-- [["Grand Place"], ["Palais Royal"]]
SELECT jsonbset '[{"location":"Point(1 1)", "PoIs": ["Grand Place", "La Bourse"],
"location":"Point(2 2)", "PoIs": ["Palais Royal", "Manneken Pis"]]' #-
ARRAY[text "\"PoIs\"", "1"];
-- ["La Bourse", "Manneken Pis"]
Como se muestra a continuación, el resultado de una operación de eliminación puede ser un registro vacío o un arreglo vacío. Para eliminar estos valores, puede utilizarse la función minusValue.
SELECT jsonbset '{[{"unit": "km", "speed": 10, "light": true},
{"unit": "km", "speed": 20}]}' - ARRAY[text 'unit', 'speed'];
-- {[{"light": true}, {}]}
SELECT jsonbset '[["Grand Place", "La Bourse"],
["Palais Royal"]]' - 0;
-- [["La Bourse"], []]
SELECT minusValues(jsonbset '{[{"unit": "km", "speed": 10, "light": true},
{"unit": "km", "speed": 20}]}' - ARRAY[text 'unit', 'speed'],
jsonbset '{"[]","{}"}');
-- {[{"light": true}, {"light": true})}
SELECT minusValues(jsonbset '[["Grand Place", "La Bourse"],
["Palais Royal"]]' - 0, jsonbset '{"[]","{}"}');
-- {[["La Bourse"], ["La Bourse"])}
Existencia JSONB temporal
jsonbset ? jsonb → tbool
jsonbset ?| jsonb[] → tbool
jsonbset ?& jsonb[] → tbool
Como en PostgreSQL, los operadores ?| y ?& comprueban, respectivamente, si alguna o todas las cadenas del arreglo de texto existen como claves de nivel superior o elementos de arreglo.
SELECT jsonbset '"{\"geom\": \"Point(1 1)\"}"' ? text 'geom';
-- t
SELECT jsonbset '{{"geom": "Point(1 1)"}, {"geom": "Point(2 2)"},
{"geom": "Point(1 1)"}}' ?| ARRAY[text 'geom'];
-- {t, t, t}
SELECT jsonbset '[{"speed": 10, "units": "km/h"},
{"speed": 20, "units": "km/h"}]' ?& ARRAY['speed', 'units'];
-- {[t, t]}
Contiene/contenido JSONB temporal
{jsonbset,jsonb} @> {jsonbset,jsonb} → tbool
{jsonbset,jsonb} <@ {jsonbset,jsonb} → tbool
SELECT jsonbset '"{\"geom\": \"Point(1 1)\"}"' @> jsonb '{"geom": "Point(1 1)"}';
-- t
SELECT jsonbset '{{"geom": "Point(1 1)"}, {"geom": "Point(2 2)"},
{"geom": "Point(1 1)"}}' @> jsonb '{"geom": "Point(1 1)"}';
-- {t, f, t}
SELECT jsonb '{"geom": "Point(1 1)"}' <@ jsonbset '{[{"geom": "Point(1 1)"},
{"geom": "Point(1 1)"}, {"geom": "Point(1 1)"}],
[{"geom": "Point(2 2)"}, {"geom": "Point(2 2)"}]}';
-- {[t, t], [f, f]}
Asignación JSONB temporal
jsonbset_set(jsonbset,path text[],jsonb,create boolean=true) → jsonbset
jsonbset_set_lax(jsonbset,path text[],jsonb,create boolean=true,handle_null text) → jsonbset
Devuelve el valor jsonbset con el elemento especificado por la ruta reemplazado por jsonb, o añadido si create es verdadero y el elemento no existe. Todos los pasos anteriores de la ruta deben existir, o se devuelve el objetivo sin cambios. Como ocurre con los operadores orientados a rutas, los enteros negativos que aparecen en la ruta cuentan desde el final de los arreglos JSON. Si el último paso de la ruta es un índice de arreglo que está fuera de rango, y create_if_missing es verdadero, el nuevo valor se añade al principio del arreglo si el índice es negativo, o al final del arreglo si es positivo.
La función jsonbset_set_lax se comporta de forma idéntica a jsonbset_set si el valor dado no es NULL; de lo contrario, se comporta según el valor de handle_null, que debe ser uno de 'raise_exception', 'use_json_null', 'delete_key' o 'return_source'. Como se indicó anteriormente, mantuvimos el comportamiento de PostgreSQL para esta función habilitando el valor 'return_source', mientras que para todas las demás operaciones de consulta, este valor se ha reemplazado por 'return_null'.
SELECT jsonbset_set(jsonbset '[{"speed":10}, {"speed":20}]',
ARRAY['units'], '"km/h"'::jsonb);
-- [{"speed": 10, "units": "km/h"}, {"speed": 20, "units": "km/h"}]
SELECT jsonbset_set(jsonbset '[{"speed":10}, {"speed":20}]',
ARRAY['units'], '"km/h"'::jsonb, false);
-- [{"speed": 10}, {"speed": 20}]
SELECT jsonbset_set(jsonbset '[{"speed": 10, "units": "km/h"},
{"speed": 20, "units": "km/h"}]', ARRAY['units'], '"mi/h"'::jsonb);
-- [{"speed": 10, "units": "mi/h"}, {"speed": 20, "units": "mi/h"}]
SELECT jsonbset_set_lax(jsonbset '[{"speed": 10, "units": "km/h"},
{"speed": 20}]', ARRAY['units'], 'null'::jsonb, true, 'delete_key');
-- [{"speed": 10, "units": "km/h"}, {"speed": 20}]
Inserción JSONB temporal
jsonbset_insert(jsonbset,path text[],jsonb,after boolean=false) → jsonbset
Devuelve el valor jsonbset con jsonb insertado. Si el elemento especificado por la ruta es un elemento de arreglo, el nuevo valor se insertará antes de ese elemento si after es falso, o después en caso contrario. Si el elemento especificado por la ruta es un campo de objeto, el nuevo valor se insertará solo si el objeto no contiene ya esa clave. Todos los pasos anteriores de la ruta deben existir, o se devuelve el objetivo sin cambios. Como ocurre con los operadores orientados a rutas, los enteros negativos que aparecen en la ruta cuentan desde el final de los arreglos JSON. Si el último paso de la ruta es un índice de arreglo que está fuera de rango, el nuevo valor se añade al principio del arreglo si el índice es negativo, o al final del arreglo si es positivo.
SELECT jsonbset_insert(jsonbset '[{"speed":10}, {"speed":20}]',
ARRAY['units'], '"km/h"'::jsonb);
-- [{"speed": 10, "units": "km/h"}, {"speed": 20, "units": "km/h"}]
SELECT jsonbset_insert(jsonbset '[{"speed":10}, {"speed":20}]',
ARRAY['units'], '"km/h"'::jsonb, false);
-- [{"speed": 10}, {"speed": 20}]
SELECT jsonbset_insert(jsonbset '[{"speed": 10, "units": "km/h"},
{"speed": 20, "units": "km/h"}]', ARRAY['units'], '"mi/h"'::jsonb);
-- [{"speed": 10, "units": "mi/h"}, {"speed": 20, "units": "mi/h"}]
Devolver un conjunto JSONB sin nulos
jsonbset_strip_nulls(jsonbset,strip_in_arrays bool=false) → jsonbset
El último argumento indica si los elementos de arreglo nulos también se eliminan. Los valores nulos simples nunca se eliminan.
SELECT jsonbset_strip_nulls(textset '[{"speed": 10, "lights": null},
{"speed": 20, "PoIs": ["Grand Place", "La Bourse", null]}]');
/* [{"speed": 10},
{"PoIs": ["Grand Place", "La Bourse", null], "speed": 20}] */
SELECT jsonbset_strip_nulls(jsonbset '[{"speed": 10, "lights": null},
{"speed": 20, "PoIs": ["Grand Place", "La Bourse", null]}]', true);
/* [{"speed": 10},
{"PoIs": ["Grand Place", "La Bourse"], "speed": 20}] */
SELECT jsonbset_strip_nulls(jsonbset
'{{"road": "Bvd Gén. Jacques", "category": "primary"},
{"road": "Bvd de la Cambre", "category": "primary"},
{"road": "rue de l''Abbaye", "category": null}}');
/* [{"road": "Bvd Gén. Jacques", "category": "primary"},
{"road": "Bvd de la Cambre", "category": "primary"},
{"road": "rue de l'Abbaye"}] */
Como se muestra a continuación, la función no elimina los valores nulos simples. Para este propósito puede utilizarse la función minusValue.
SELECT jsonbset_strip_nulls(textset '[{"speed": 10}, null,
{"speed": 20, "PoIs": ["Grand Place", "La Bourse"]}]');
/* [{"speed":10}, null,
{"speed":20,"PoIs":["Grand Place","La Bourse"]}] */
SELECT minusValues(textset '[{"speed": 10}, null,
{"speed": 20, "PoIs": ["Grand Place", "La Bourse"]}]', 'null');
/* {[{"speed": 10}, {"speed": 10}),
[{"speed": 20, "PoIs": ["Grand Place", "La Bourse"]}]}
¿Devuelve la ruta JSON algún elemento para el conjunto JSONB especificado?
jsonbset @? jsonpath → tbool
jsonbset_path_exists(jsonbset,vars jsonb='{}',silent boolean=false) → tbool
jsonbset_path_exists_tz(jsonbset,vars jsonb='{}',silent boolean=false) → tbool
El operador @? suprime los siguientes errores: campo de objeto o elemento de arreglo faltante, tipo de elemento JSON inesperado, errores de fecha/hora y numéricos. Las funciones anteriores también pueden suprimir estos tipos de errores estableciendo el último argumento en verdadero. Este comportamiento puede ser útil al buscar en colecciones de documentos JSON de estructura variable.
SELECT jsonbset '[{"speed":10}, {"speed": 20, "units": "km/h"}]' @?
'$.units ? (@ == "km/h")';
-- [f, t]
SELECT jsonbset_path_exists(jsonbset
'{{"road": "Bvd Gén. Jacques", "category": "primary"},
{"road": "Bvd Gén. Jacques", "category": "primary"},
{"road": "Bvd de la Cambre", "category": "residential"}}',
'$.category ? (@ == "residential")');
-- {f, f, t}
-- TODO, it works without ".datetime()"
SELECT jsonbset_path_exists_tz(jsonbset
'[{"speed":10, "cameraId": "25", "lastInspection": "2000-08-01 12:00:00"},
{"speed":20, "cameraId": "35", "lastInspection": "2000-03-01 12:00:00"}]',
'$.lastInspection ? (@.datetime() > "2000-06-01".datetime() )');
-- [t, f]
Devolver el resultado de una comprobación de predicado de ruta JSON para un conjunto JSONB
jsonbset @@ jsonpath → tbool
jsonbset_path_match(jsonbset,vars jsonb='{}',silent boolean=false) → tbool
jsonbset_path_match_tz(jsonbset,vars jsonb='{}',silent boolean=false) → tbool
El operador @@ suprime los siguientes errores: campo de objeto o elemento de arreglo faltante, tipo de elemento JSON inesperado, errores de fecha/hora y numéricos. Las funciones anteriores también pueden suprimir estos tipos de errores estableciendo el último argumento en verdadero. Este comportamiento puede ser útil al buscar en colecciones de documentos JSON de estructura variable.
SELECT jsonbset '[{"speed":10}, {"speed": 20, "units": "km/h"}]' @@
'$.units == "km/h"';
-- [f, t]
SELECT jsonbset_path_match(jsonbset
'{{"road": "Bvd Gén. Jacques", "category": "primary"},
{"road": "Bvd Gén. Jacques", "category": "primary"},
{"road": "Bvd de la Cambre", "category": "residential"}}',
'$.category == "residential"');
-- {f, f, t}
-- TODO, it works without ".datetime()"
SELECT jsonbset_path_match_tz(jsonbset
'[{"speed":10, "cameraId": "25", "lastInspection": "2000-08-01 12:00:00"},
{"speed":20, "cameraId": "35", "lastInspection": "2000-03-01 12:00:00"}]',
'$.lastInspection.datetime() > "2000-06-01".datetime()');
-- [t, f]
Devolver todos los elementos devueltos por una ruta JSON de un conjunto JSONB, como un arreglo JSON
jsonbset_path_query_array(jsonbset,vars jsonb='{}',silent boolean=false) → jsonbset
jsonbset_path_query_array_tz(jsonbset,vars jsonb='{}',silent boolean=false) → jsonbset
La función anterior puede suprimir los siguientes errores estableciendo el último argumento en verdadero: campo de objeto o elemento de arreglo faltante, tipo de elemento JSON inesperado, errores de fecha/hora y numéricos. Este comportamiento puede ser útil al buscar en colecciones de documentos JSON de estructura variable.
SELECT jsonbset_path_query_array(jsonbset
'[{"speed":10}, {"speed": 20, "units": "km/h"}]',
'$ ? (@.speed >= $min && @.speed <= $max)', '{"min":10, "max":30}');
-- [[{"speed": 10}], [{"speed": 20, "units": "km/h"}]]
-- TODO, it works without "_tz"
SELECT jsonbset_path_query_array_tz(jsonbset
'[{"cameraId":25, "inspections":["2000-01-03", "2000-01-06", "2000-01-09"]},
{"cameraId":35, "inspections":["2000-01-04", "2000-01-07", "2000-01-10"]}]',
'$.inspections ? (@.timestamp_tz() >= $min.timestamp_tz() &&
@.timestamp_tz() <= $max.timestamp_tz())', '{"min":"2000-01-05", "max":"2000-01-09"}');
-- [["2000-01-06", "2000-01-09"], ["2000-01-07"]]
Devolver el primer elemento devuelto por una ruta JSON de un conjunto JSONB
jsonbset_path_query_first(jsonbset,vars jsonb='{}',silent boolean=false) → jsonbset
jsonbset_path_query_first_tz(jsonbset,vars jsonb='{}',silent boolean=false) → jsonbset
Las funciones anteriores pueden suprimir los siguientes errores estableciendo el último argumento en verdadero: campo de objeto o elemento de arreglo faltante, tipo de elemento JSON inesperado, errores de fecha/hora y numéricos. Este comportamiento puede ser útil al buscar en colecciones de documentos JSON de estructura variable.
SELECT jsonbset_path_query_first(jsonbset
'[{"speed":10}, {"speed": 20, "units": "km/h"}]',
'$.speed ? (@ >= $min && @ <= $max'), '{"min":10, "max":30}');
-- [[{"speed": 10}], [{"speed": 20, "units": "km/h"}]]
-- TODO, it works without "_tz"
SELECT jsonbset_path_query_array_tz(jsonbset
'[{"cameraId":25, "inspections":["2000-01-03", "2000-01-06", "2000-01-09"]},
{"cameraId":35, "inspections":["2000-01-04", "2000-01-07", "2000-01-10"]}]',
'$.inspections ? (@.datetime() >= $min.datetime() && @.datetime() <= $max.datetime())',
'{"min":"2000-01-05", "max":"2000-01-09"}');
-- [["2000-01-06", "2000-01-09"], ["2000-01-07"]]