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 jsonbsetSetLax, 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 jsonbsetObjectField, el último argumento especifica el comportamiento en el caso en que la función devuelve NULL. Ilustramos este comportamiento para la función jsonbsetObjectField a continuación.
Extrae un campo de objeto JSON especificado por una clave
{textset,jsonbset} -> text → {textset,jsonbset}
jsonbset ->> text → textset
jsonbsetObjectField(jsonbset,text,null_handle text='use_json_null') → jsonbset
jsonbsetObjectFieldText(jsonbset,text,null_handle text='use_json_null') → textset
SELECT set(ARRAY[jsonb '{"unit": "km", "speed": 10}',
'{"unit": "km", "speed": 20}']) -> text 'speed';
-- {10, 20}
SELECT set(ARRAY[jsonb '{"position":"Point(1 1)", "speed":10}',
'{"position":"Point(2 2)", "speed":20}']) ->> text 'position';
-- {"Point(1 1)", "Point(2 2)"}
SELECT set(ARRAY[jsonb '{"unit": "km", "speed": 10}', '{"unit": "km"}',
'{"unit": "km", "speed": 10}']) -> text 'speed';
-- {"null", 10}
Mostramos a continuación el comportamiento de la función jsonbsetObjectField 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 jsonbsetObjectField(set(ARRAY[jsonb '{"unit": "km", "speed": 10}',
'{"unit": "km"}', '{"unit": "km", "speed": 10}']), 'speed', 'raise_exception');
-- ERROR: The lifted operation returned NULL
SELECT jsonbsetObjectField(set(ARRAY[jsonb '{"unit": "km", "speed": 10}',
'{"unit": "km"}', '{"unit": "km", "speed": 10}']), 'speed', 'use_json_null');
-- {"null", 10}
SELECT jsonbsetObjectField(set(ARRAY[jsonb '{"unit": "km", "speed": 10}',
'{"unit": "km"}', '{"unit": "km", "speed": 10}']), 'speed', 'delete_key');
-- {10}
SELECT jsonbsetObjectField(set(ARRAY[jsonb '{"unit": "km", "speed": 10}',
'{"unit": "km"}', '{"unit": "km", "speed": 10}']), 'speed', 'return_null');
-- NULL
Extrae un campo de objeto JSON especificado por una ruta
{textset,jsonbset} #> text[] → {textset,jsonbset}
jsonbsetExtractPath(jsonbset,path text[],null_handle text='use_json_null') → jsonbset
jsonbsetExtractPathText(jsonbset,path text[],null_handle text='use_json_null') → textset
SELECT set(ARRAY[jsonb '{"speed": {"unit": "km", "value": 10}}',
'{"speed": {"unit": "km", "value": 20}}']) #> ARRAY[text 'speed', 'value'];
-- {10, 20}
SELECT set(ARRAY[jsonb '{"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"}
Extrae un elemento de un arreglo JSON
jsonbsetArrayElement(jsonbset,integer,null_handle text='use_json_null') → jsonbset jsonbsetArrayElementText(jsonbset,integer,null_handle text='use_json_null') → jsonbset
SELECT jsonbsetArrayElement(set(ARRAY[jsonb '["Grand Place", "La Bourse"]',
'["Palais Royal", "Manneken Pis"]']), 0);
-- {"\"Grand Place\"", "\"Palais Royal\""}
SELECT jsonbsetArrayElement(set(ARRAY[jsonb '["Grand Place", "La Bourse"]',
'["Palais Royal", "Manneken Pis"]']), 1);
-- {"\"La Bourse\"", "\"Manneken Pis\""}
Extrae 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 de un conjunto JSONB
{jsonb,jsonbset} || {jsonbset,jsonb} → jsonbset
El valor se concatena con cada elemento del conjunto, y el orden de los operandos es libre.
SELECT set(ARRAY[jsonb '{"speed":10}', '{"speed":20}']) || '{"unit":"km"}'::jsonb;
-- {"{\"unit\": \"km\", \"speed\": 10}", "{\"unit\": \"km\", \"speed\": 20}"}
SELECT jsonb '{"unit":"km"}' || set(ARRAY[jsonb '{"speed":10}', '{"speed":20}']);
-- {"{\"unit\": \"km\", \"speed\": 10}", "{\"unit\": \"km\", \"speed\": 20}"}
Eliminación en un conjunto JSONB
jsonbset - {int,text,text[]} → jsonbset
jsonbset #- text[] → jsonbset
SELECT set(ARRAY[jsonb '{"unit": "km", "speed": 10}',
'{"unit": "km", "speed": 20}']) - text 'unit';
-- {"{\"speed\": 10}", "{\"speed\": 20}"}
SELECT set(ARRAY[jsonb '["Grand Place", "La Bourse"]',
'["Palais Royal", "Manneken Pis"]']) - 1;
-- {"[\"Grand Place\"]", "[\"Palais Royal\"]"}
SELECT set(ARRAY[jsonb '{"location":"Point(1 1)", "PoIs": ["Grand Place", "La Bourse"]}',
'{"location":"Point(2 2)", "PoIs": ["Palais Royal", "Manneken Pis"]}'])
#- ARRAY[text 'PoIs', '1'];
/* {"{\"PoIs\": [\"Grand Place\"], \"location\": \"Point(1 1)\"}", "{\"PoIs\":
[\"Palais Royal\"], \"location\": \"Point(2 2)\"}"} */
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 setMinus.
SELECT set(ARRAY[jsonb '{"unit": "km", "speed": 10, "light": true}',
'{"unit": "km", "speed": 20}']) - ARRAY[text 'unit', 'speed'];
-- {"{}", "{\"light\": true}"}
SELECT set(ARRAY[jsonb '["Grand Place", "La Bourse"]', '["Palais Royal"]']) - 0;
-- {[], "[\"La Bourse\"]"}
SELECT setMinus(set(ARRAY[jsonb '{"unit": "km", "speed": 10, "light": true}',
'{"unit": "km", "speed": 20}']) - ARRAY[text 'unit', 'speed'],
set(ARRAY[jsonb '{}', '[]']));
-- {"{\"light\": true}"}
SELECT setMinus(set(ARRAY[jsonb '["Grand Place", "La Bourse"]', '["Palais Royal"]']) - 0,
set(ARRAY[jsonb '{}', '[]']));
-- {"[\"La Bourse\"]"}
Existencia en un conjunto JSONB
jsonbset ? text → boolean[]
jsonbset {?|, ?&} text[] → boolean[]
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. El resultado es un booleano por cada valor del conjunto, en el orden de los valores del conjunto.
SELECT jsonbset '{"{\"speed\": 10}", "{\"speed\": 20, \"units\": \"km/h\"}"}';
-- {"{\"speed\": 10}", "{\"speed\": 20, \"units\": \"km/h\"}"}
SELECT jsonbset '{"{\"speed\": 10}",
"{\"speed\": 20, \"units\": \"km/h\"}"}' ? text 'units';
-- {f,t}
SELECT jsonbset '{"{\"speed\": 10}",
"{\"speed\": 20, \"units\": \"km/h\"}"}' ?| ARRAY[text 'units', text 'lights'];
-- {f,t}
SELECT jsonbset '{"{\"speed\": 10}",
"{\"speed\": 20, \"units\": \"km/h\"}"}' ?& ARRAY[text 'speed', text 'units'];
-- {f,t}
Asignación en un conjunto JSONB
jsonbsetSet(jsonbset,path text[],val jsonb,create_missing boolean=true) → jsonbset jsonbsetSetLax(jsonbset,path text[],val 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 jsonbsetSetLax se comporta de forma idéntica a jsonbsetSet 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 jsonbsetSet(set(ARRAY[jsonb '{"speed":10}', '{"speed":20}']), ARRAY['units'],
'"km/h"'::jsonb);
-- {"{\"speed\": 10, \"units\": \"km/h\"}", "{\"speed\": 20, \"units\": \"km/h\"}"}
SELECT jsonbsetSet(set(ARRAY[jsonb '{"speed":10}', '{"speed":20}']), ARRAY['units'],
'"km/h"'::jsonb, false);
-- {"{\"speed\": 10}", "{\"speed\": 20}"}
SELECT jsonbsetSet(set(ARRAY[jsonb '{"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 jsonbsetSetLax(set(ARRAY[jsonb '{"speed": 10, "units": "km/h"}', '{"speed": 20}']),
ARRAY['units'], 'null'::jsonb, true, 'delete_key');
-- {"{\"speed\": 10}", "{\"speed\": 20}"}
Devuelve el valor jsonbset con jsonb insertado
jsonbsetInsert(jsonbset,path text[],val jsonb,after boolean=false) → jsonbset
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 jsonbsetInsert(set(ARRAY[jsonb '{"speed":10}', '{"speed":20}']), ARRAY['units'],
'"km/h"'::jsonb);
-- {"{\"speed\": 10}", "{\"speed\": 20}"}
SELECT jsonbsetInsert(set(ARRAY[jsonb '{"speed":10}', '{"speed":20}']), ARRAY['units'],
'"km/h"'::jsonb, false);
-- {"{\"speed\": 10}", "{\"speed\": 20}"}
SELECT jsonbsetInsert(set(ARRAY[jsonb '{"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\"}"}
Devuelve un conjunto JSONB sin nulos
jsonbsetStripNulls(jsonbset,strip_in_arrays boolean=false) → jsonbset
El último argumento indica si los elementos de arreglo nulos también se eliminan. Los valores nulos simples nunca se eliminan.
SELECT jsonbsetStripNulls(set(ARRAY[jsonb '{"speed": 10, "lights": null}',
'{"speed": 20, "PoIs": ["Grand Place", "La Bourse", null]}']));
/* {"{\"speed\": 10}", "{\"PoIs\": [\"Grand Place\", \"La Bourse\", null], \"speed\":
20}"} */
SELECT jsonbsetStripNulls(set(ARRAY[jsonb '{"speed": 10, "lights": null}',
'{"speed": 20, "PoIs": ["Grand Place", "La Bourse", null]}']), true);
-- {"{\"speed\": 10}", "{\"PoIs\": [\"Grand Place\", \"La Bourse\"], \"speed\": 20}"}
SELECT jsonbsetStripNulls(set(ARRAY[jsonb
'{"road": "Bvd Gén. Jacques", "category": "primary"}',
'{"road": "Bvd de la Cambre", "category": "primary"}',
'{"road": "rue de l''Abbaye", "category": null}']));
/* {"{\"road\": \"rue de l'Abbaye\"}", "{\"road\": \"Bvd Gén. Jacques\", \"category\":
\"primary\"}", "{\"road\": \"Bvd de la Cambre\", \"category\": \"primary\"}"} */
Como se muestra a continuación, la función no elimina los valores nulos simples. Para este propósito puede utilizarse la función setMinus.
SELECT jsonbsetStripNulls(set(ARRAY[jsonb '{"speed": 10}', 'null',
'{"speed": 20, "PoIs": ["Grand Place", "La Bourse"]}']));
/* {"null", "{\"speed\": 10}", "{\"PoIs\": [\"Grand Place\", \"La Bourse\"], \"speed\":
20}"} */
SELECT setMinus(set(ARRAY[jsonb '{"speed": 10}', 'null',
'{"speed": 20, "PoIs": ["Grand Place", "La Bourse"]}']), set(ARRAY[jsonb 'null']));
-- {"{\"speed\": 10}", "{\"PoIs\": [\"Grand Place\", \"La Bourse\"], \"speed\": 20}"}
¿Devuelve la ruta JSON algún elemento para el conjunto JSONB especificado?
jsonbset @? jsonpath → boolean[]
jsonbsetPathExists(jsonbset,vars jsonb='{}',silent boolean=false) → boolean[]
jsonbsetPathExistsTz(jsonbset,vars jsonb='{}',silent boolean=false) → boolean[]
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. El resultado es un booleano por cada valor del conjunto, en el orden de los valores del conjunto.
SELECT jsonbset '{"{\"speed\": 10}", "{\"speed\": 20, \"units\": \"km/h\"}"}';
-- {"{\"speed\": 10}", "{\"speed\": 20, \"units\": \"km/h\"}"}
SELECT jsonbset '{"{\"speed\": 10}",
"{\"speed\": 20, \"units\": \"km/h\"}"}' @? '$.units';
-- {f,t}
SELECT jsonbsetPathExists(jsonbset '{"{\"speed\": 10}",
"{\"speed\": 20, \"units\": \"km/h\"}"}', '$.speed ? (@ > $min)', '{"min": 15}');
-- {f,t}
Devuelve el resultado de una comprobación de predicado de ruta JSON para un conjunto JSONB
jsonbset @@ jsonpath → boolean[]
jsonbsetPathMatch(jsonbset,vars jsonb='{}',silent boolean=false) → boolean[]
jsonbsetPathMatchTz(jsonbset,vars jsonb='{}',silent boolean=false) → boolean[]
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. El resultado es un booleano por cada valor del conjunto, en el orden de los valores del conjunto.
SELECT jsonbset '{"{\"speed\": 10}", "{\"speed\": 20, \"units\": \"km/h\"}"}';
-- {"{\"speed\": 10}", "{\"speed\": 20, \"units\": \"km/h\"}"}
SELECT jsonbset '{"{\"speed\": 10}",
"{\"speed\": 20, \"units\": \"km/h\"}"}' @@ '$.speed > 15';
-- {f,t}
SELECT jsonbsetPathMatch(jsonbset '{"{\"speed\": 10}",
"{\"speed\": 20, \"units\": \"km/h\"}"}', '$.speed > $min', '{"min": 15}');
-- {f,t}
Devuelve todos los elementos devueltos por una ruta JSON de un conjunto JSONB, como un arreglo JSON
jsonbsetPathQueryArray(jsonbset,vars jsonb='{}',silent boolean=false) → jsonbset
jsonbsetPathQueryArrayTz(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 jsonbsetPathQueryArray(set(ARRAY[jsonb '{"speed":10}',
'{"speed": 20, "units": "km/h"}']), '$ ? (@.speed >= $min && @.speed <= $max)',
'{"min":10, "max":30}');
-- {"[{\"speed\": 10}]", "[{\"speed\": 20, \"units\": \"km/h\"}]"}
SELECT jsonbsetPathQueryArrayTz(set(ARRAY[jsonb
'{"cameraId":25, "inspections":["2001-01-03", "2001-01-06", "2001-01-09"]}',
'{"cameraId":35, "inspections":["2001-01-04", "2001-01-07", "2001-01-10"]}']),
'$.inspections ? (@.timestamp_tz() >= $min.timestamp_tz() &&
@.timestamp_tz() <= $max.timestamp_tz())', '{"min":"2001-01-05", "max":"2001-01-09"}');
-- {"[\"2001-01-07\"]", "[\"2001-01-06\", \"2001-01-09\"]"}
Devuelve el primer elemento devuelto por una ruta JSON de un conjunto JSONB
jsonbsetPathQueryFirst(jsonbset,vars jsonb='{}',silent boolean=false) → jsonbset
jsonbsetPathQueryFirstTz(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 jsonbsetPathQueryFirst(set(ARRAY[jsonb '{"speed":10}',
'{"speed": 20, "units": "km/h"}']), '$.speed ? (@ >= $min && @ <= $max)',
'{"min":10, "max":30}');
-- {10, 20}
SELECT jsonbsetPathQueryArrayTz(set(ARRAY[jsonb
'{"cameraId":25, "inspections":["2001-01-03", "2001-01-06", "2001-01-09"]}',
'{"cameraId":35, "inspections":["2001-01-04", "2001-01-07", "2001-01-10"]}']),
'$.inspections ? (@.datetime() >= $min.datetime() && @.datetime() <= $max.datetime())',
'{"min":"2001-01-05", "max":"2001-01-09"}');
-- {"[\"2001-01-07\"]", "[\"2001-01-06\", \"2001-01-09\"]"}