Después del
ajuste del comité 2019-03 , se llevó a cabo la congelación de características. Aquí tenemos una columna casi tradicional:
ya escribimos sobre la congelación del año pasado. Ahora, los resultados de 2019: cuál de los nuevos se incluirá en PostgreSQL 12. En esta parte de la revisión de JSONPath, se utilizan ejemplos y fragmentos del informe "Postgres 12 in Etudes", que Oleg Bartunov leyó en
Saint Highload ++ en San Petersburgo el 9 de abril de este año.
Jsonpath
Todo lo relacionado con JSON (B) es relevante, tiene una gran demanda en el mundo, en Rusia, y esta es una de las áreas más importantes de desarrollo en Postgres Professional. El tipo jsonb, las funciones y los operadores para trabajar con JSON / JSONB aparecieron en PostgreSQL versión 9.4, fueron creados por un equipo dirigido por Oleg Bartunov.
El estándar SQL / 2016 permite trabajar con JSON: allí se menciona JSONPath: un conjunto de herramientas de direccionamiento de datos dentro de JSON; JSONTABLE: medio de convertir JSON en tablas normales; Una gran familia de funciones y operadores. A pesar de que JSON en Postgres ha sido compatible durante mucho tiempo, en 2017 Oleg Bartunov y sus colegas comenzaron a trabajar para admitir el estándar. Cumplir con el estándar siempre es bueno. De todo lo que se describe en el estándar, solo uno, pero el parche más importante es JSONPath en la versión 12, por lo que hablaremos de ello en primer lugar.
En la antigüedad, la gente usaba JSON y lo almacenaba en campos de texto. En 9.3, apareció un tipo de datos especial para JSON, pero la funcionalidad asociada con él no era rica, y las solicitudes con este tipo funcionaron lentamente debido al tiempo dedicado a analizar la representación de texto de JSON. Esto detuvo a muchos usuarios potenciales de Postgres que preferían las bases de datos NoSQL. La productividad de Postgres aumentó en 9.4 cuando, gracias a O. Bartunov, A. Korotkov y F. Sigaev, Postgres introdujo una versión binaria de JSON, el tipo jsonb.
No es necesario analizar jsonb cada vez, por lo que trabajar con él es mucho más rápido. De las nuevas funciones y operadores que surgieron al mismo tiempo, algunos funcionan solo con un nuevo tipo binario, como el operador importante de ocurrencia
@> , que verifica si un elemento o matriz está incluido en un JSONB dado:
SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;
da VERDADERO, ya que la matriz del lado derecho ingresa a la matriz de la izquierda. Pero
SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;
dará FALSO, ya que el nivel de anidamiento es diferente, debe establecerse explícitamente. ¿Se introdujo el operador de existencia para el tipo jsonb
? (un signo de interrogación) que comprueba si una cadena es una clave de objeto o un elemento de una matriz en el nivel superior de los valores JSONB, así como dos operadores similares más (detalles
aquí ). Son compatibles con los índices GIN con dos clases de operadores GIN. El operador
-> (flecha) le permite "navegar" a través de JSONB, devuelve un valor por clave o, si es una matriz, por índice. Hay
varios operadores
más para mudarse. Pero no hay forma de organizar filtros que funcionen de manera similar a WHERE. Fue un gran avance: gracias a jsonb, Postgres comenzó a crecer en popularidad como RDBMS con funciones NoSQL.
En 2014, A. Korotkov, O. Bartunov y F. Sigaev desarrollaron la extensión jsquery, que se incluyó como resultado en Postgres Pro Standard 9.5 (y en versiones posteriores de Standard y Enterprise). Proporciona características adicionales muy amplias para trabajar con json (b). Esta extensión define el lenguaje de consulta para extraer datos de json (b) e índices para acelerar estas consultas. Los usuarios requerían esta funcionalidad, no estaban listos para esperar el estándar y la inclusión de nuevas características en la versión estándar. El valor práctico también se evidencia por el hecho de que el desarrollo fue patrocinado por Wargaming.net. La extensión implementa un tipo especial: jsquery.
Una consulta en este idioma es compacta y se ve, por ejemplo, así:
SELECT '{"apt":[{"no": 1, "rooms":2}, {"no": 2, "rooms":3}, {"no": 3, "rooms":2}]}'::jsonb @@ 'apt.#.rooms=3'::jsquery;
Estamos preguntando aquí si hay "tres rublos" en el edificio de apartamentos. El tipo jsquery debe especificarse porque el operador @@ ahora también está en el tipo jsonb. La descripción está
aquí , y la presentación con muchos ejemplos está
aquí .
Total: Postgres ya tenía todo para trabajar con JSON, y luego apareció el estándar SQL: 2016. Resultó que su semántica no es tan diferente de la nuestra en la extensión jsquery. Es posible que los autores del estándar incluso hayan mirado a jsquery, inventando JSONPath. Nuestro equipo tuvo que implementar un poco diferente de lo que ya teníamos y, por supuesto, muchas cosas nuevas también.
Hace más de un año, en el commitfest de marzo, los frutos de nuestros esfuerzos de programación se ofrecieron a la comunidad en forma de 3 parches grandes con soporte para el estándar
SQL: 2016 :
SQL / JSON: JSONPath;
SQL / JSON: funciones;
SQL / JSON: JSON_TABLE.
Pero desarrollar un parche no es todo el negocio, promocionarlos tampoco es fácil, especialmente si los parches son grandes y afectan a muchos módulos. Se requieren muchas iteraciones de revisión, revisión, el parche debe promoverse, como lo hacen las empresas comerciales, invirtiendo muchos recursos (horas-hombre). El arquitecto jefe de Postgres Professional, Alexander Korotkov, asumió la responsabilidad (ya que ahora tiene el estado de un committer) y aseguró la adopción del parche JSONPath, el principal en esta serie de parches. El segundo y el tercero ahora están en el estado de Revisión de necesidades. JSONPath enfocado le permite trabajar con la estructura JSON (B) y es lo suficientemente flexible como para resaltar sus fragmentos. De los 15 puntos prescritos en el estándar, 14 están implementados, y esto es más que en Oracle, MySQL y MS SQL.
La notación JSONPath difiere de las declaraciones de Postgres para trabajar con notación JSON y JSQuery. La jerarquía se indica mediante puntos:
$ .abc (en notación postgres 11, tendría que escribir 'a' -> 'b' -> 'c');
$ - el contexto actual del elemento - de hecho, la expresión con $ define la región json (b) que se va a procesar, incluida la del filtro, el resto no está disponible para trabajar;
@ - el contexto actual en la expresión del filtro - itera sobre las rutas disponibles en la expresión con $;
[*] - una matriz;
* - comodín, en la expresión con $ o @ significa cualquier valor del segmento de ruta, pero teniendo en cuenta la jerarquía;
** - como parte de la expresión con $ o @ puede significar cualquier valor del segmento de ruta sin tener en cuenta la jerarquía; es conveniente usarlo si no conoce el nivel de anidamiento de elementos;
operador "?" le permite organizar un filtro similar a DONDE:
$ .abc? (@ .x> 10);
$ .abcxtype (), así como size (), double (), ceiling (), floor (), abs (), datetime (), keyvalue () son métodos.
Una consulta con la función jsonb_path_query (sobre las funciones a continuación) podría verse así:
SELECT jsonb_path_query_array('[1,2,3,4,5]', '$[*] ? (@ > 3)'); jsonb_path_query_array
Aunque no se ha comprometido un parche especial con funciones, el parche JSONPath ya tiene funciones clave para trabajar con JSON (B):
jsonb_path_exists('{"a": 1}', '$.a') true ( "?") jsonb_path_exists('{"a": 1}', '$.b') false jsonb_path_match('{"a": 1}', '$.a == 1') true ( "@>") jsonb_path_match('{"a": 1}', '$.a >= 2') false jsonb_path_query('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 2)') 3, 4, 5 jsonb_path_query('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 5)') 0 jsonb_path_query_array('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 2)') [3, 4, 5] jsonb_path_query_array('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 5)') [] jsonb_path_query_first('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 2)') 3 jsonb_path_query_first('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 5)') NULL
Tenga en cuenta que la igualdad en las expresiones JSONPath es un solo "=", mientras que en jsquery es doble: "==".
Para ilustraciones más elegantes, generaremos JSONB en una placa de una sola columna:
CREATE TABLE house(js jsonb); INSERT INTO house VALUES ('{ "address": { "city":"Moscow", "street": "Ulyanova, 7A" }, "lift": false, "floor": [ { "level": 1, "apt": [ {"no": 1, "area": 40, "rooms": 1}, {"no": 2, "area": 80, "rooms": 3}, {"no": 3, "area": 50, "rooms": 2} ] }, { "level": 2, "apt": [ {"no": 4, "area": 100, "rooms": 3}, {"no": 5, "area": 60, "rooms": 2} ] } ] }');
Fig. 1 Vivienda del árbol JSON con apartamentos de hoja asignados.Este es un JSON extraño: tiene una jerarquía confusa, pero está tomado de la vida, y en la vida a menudo es necesario trabajar con lo que es y no con lo que debería ser. Armados con las capacidades de la nueva versión, encontraremos apartamentos en el primer y segundo piso, pero no en el primero de la lista de apartamentos del piso (en el árbol están resaltados en verde):
SELECT jsonb_path_query_array(js, '$.floor[0, 1].apt[1 to last]') FROM house;
En PostgreSQL 11, debe preguntar esto:
SELECT jsonb_agg(apt) FROM ( SELECT apt->generate_series(1, jsonb_array_length(apt) - 1) FROM ( SELECT js->'floor'->unnest(array[0, 1])->'apt' FROM house ) apts(apt) ) apts(apt);
Ahora una pregunta muy simple: ¿hay líneas que contengan (en cualquier lugar) el valor "Moscú"? Muy simple:
SELECT jsonb_path_exists(js, '$.** ? (@ == "Moscow")') FROM house;
En la versión 11, tendrías que escribir un gran script:
WITH RECURSIVE t(value) AS ( SELECT * FROM house UNION ALL ( SELECT COALESCE(kv.value, e.value) AS value FROM t LEFT JOIN LATERAL jsonb_each ( CASE WHEN jsonb_typeof(t.value) = 'object' THEN t.value ELSE NULL END ) kv ON true LEFT JOIN LATERAL jsonb_array_elements ( CASE WHEN jsonb_typeof(t.value) = 'array' THEN t.value ELSE NULL END ) e ON true WHERE kv.value IS NOT NULL OR e.value IS NOT NULL ) ) SELECT EXISTS (SELECT 1 FROM t WHERE value = '"Moscow"');
Fig. 2 Árbol de la Vivienda JSON, ¡se encontró Moscú!Estamos buscando cualquier apartamento en cualquier piso con un área de 40 a 90 metros cuadrados:
select jsonb_path_query(js, '$.floor[*].apt[*] ? (@.area > 40 && @.area < 90)') FROM house; jsonb_path_query ----------------------------------- {"no": 2, "area": 80, "rooms": 3} {"no": 3, "area": 50, "rooms": 2} {"no": 5, "area": 60, "rooms": 2} (3 rows)
Estamos buscando apartamentos con habitaciones después del 3, utilizando nuestro alojamiento Jason:
SELECT jsonb_path_query(js, '$.floor.apt.no ? (@>3)') FROM house; jsonb_path_query
Y así es como funciona jsonb_path_query_first:
SELECT jsonb_path_query_first(js, '$.floor.apt.no ? (@>3)') FROM house; jsonb_path_query_first
Vemos que solo se selecciona el primer valor que satisface la condición del filtro.
El operador booleano JSONPath para JSONB @@ se denomina operador coincidente. Calcula el predicado JSONPath llamando a la función jsonb_path_match_opr.
Otro operador booleano es @? - esta es una prueba de existencia, responde a la pregunta de si la expresión JSONPath devolverá objetos SQL / JSON, llama a la función jsonb_path_exists_opr:
'[1,2,3]' @@ '$[*] == 3' true; '[1,2,3]' @? '$[*] @? (@ == 3)' - true
Se puede lograr el mismo resultado utilizando diferentes operadores:
js @? '$.a' js @@ 'exists($.a)' js @@ '$.a == 1' js @? '$ ? ($.a == 1)'
La belleza de los operadores booleanos JSONPath es que son compatibles, acelerados por los índices GIN. jsonb_ops y jsonb_path_ops son las clases de operador correspondientes. En el ejemplo, deshabilitamos SEQSCAN, ya que tenemos un microtable, en tablas grandes, el optimizador mismo seleccionará el Índice de mapa de bits:
SET ENABLE_SEQSCAN TO OFF; CREATE INDEX ON house USING gin (js); EXPLAIN (COSTS OFF) SELECT * FROM house WHERE js @? '$.floor[*].apt[*] ? (@.rooms == 3)'; QUERY PLAN
Todas las funciones de la forma jsonb_path_xxx () tienen la misma firma:
jsonb_path_xxx( js jsonb, jsp jsonpath, vars jsonb DEFAULT '{}', silent boolean DEFAULT false )
vars es un objeto JSONB para pasar variables JSONPath:
SELECT jsonb_path_query_array('[1,2,3,4,5]', '$[*] ? (@ > $x)', vars => '{"x": 2}'); jsonb_path_query_array
Es difícil prescindir de vars cuando hacemos una unión que involucra un campo de tipo jsonb en una de las tablas. Digamos que hacemos una aplicación que busca apartamentos adecuados para los empleados en esa misma casa que han escrito sus requisitos para el área mínima en el cuestionario:
CREATE TABLE demands(name text, position text, demand int); INSERT INTO demands VALUES ('','', 85), ('',' ', 45); SELECT jsonb_path_query(js, '$.floor[*].apt[*] ? (@.area >= $min)', vars => jsonb_build_object('min', demands.demand)) FROM house, demands WHERE name = ''; -[ RECORD 1 ]
Lucky Pasha puede elegir entre 4 apartamentos. Pero vale la pena cambiar 1 letra en la solicitud, de "P" a "C", ¡y no habrá elección! Solo 1 apartamento servirá.
Queda una palabra clave más: el silencio es una bandera que suprime el manejo de errores; están en la conciencia del programador.
SELECT jsonb_path_query('[]', 'strict $.a'); ERROR: SQL/JSON member not found DETAIL: jsonpath member accessor can only be applied to an object
El error Pero esto no será un error:
SELECT jsonb_path_query('[]', 'strict $.a', silent => true); jsonb_path_query
Por cierto, acerca de los errores: de acuerdo con el estándar, los errores aritméticos en las expresiones no dan mensajes de error, están en la conciencia del programador:
SELECT jsonb_path_query('[1,0,2]', '$[*] ? (1/ @ >= 1)'); jsonb_path_query
Al calcular la expresión en el filtro, se buscan los valores de la matriz, entre los cuales hay 0, pero dividir por 0 no genera un error.
Las funciones funcionarán de manera diferente según el modo seleccionado: estricto o laxo (en la traducción "no estricto" o incluso "suelto", se selecciona de forma predeterminada). Supongamos que estamos buscando una clave en modo Lax en JSON, donde obviamente no es:
SELECT jsonb '{"a":1}' @? 'lax $.b ? (@ > 1)'; ?column?
Ahora en modo estricto:
SELECT jsonb '{"a":1}' @? 'strict $.b ? (@ > 1)'; ?column?
Es decir, donde en modo liberal recibimos FALSO, con estricto obtuvimos NULL.
En el modo Lax, una matriz con una jerarquía compleja [1,2, [3,4,5]] siempre se expande a [1,2,3,4,5]:
SELECT jsonb '[1,2,[3,4,5]]' @? 'lax $[*] ? (@ == 5)'; ?column?
En modo estricto, el número "5" no se encontrará, ya que no se encuentra en la parte inferior de la jerarquía. Para encontrarlo, debe modificar la consulta, reemplazando "@" con "@ [*]":
SELECT jsonb '[1,2,[3,4,5]]' @? 'strict $[*] ? (@[*] == 5)'; ?column?
En PostgreSQL 12, JSONPath es un tipo de datos. El estándar no dice nada sobre la necesidad de un nuevo tipo, es una propiedad de implementación. Con el nuevo tipo, obtenemos trabajo completo con jsonpath con la ayuda de operadores e índices que aceleran su trabajo, que ya existe para JSONB. De lo contrario, JSONPath tendría que integrarse a nivel del código de ejecutor y optimizador.
Puede leer sobre la sintaxis de SQL / JSON, por ejemplo,
aquí .
La publicación de blog de Oleg Bartunov trata sobre la
conformidad SQL / JSON estándar-2016 para PostgreSQL, Oracle, SQL Server y MySQL.
Aquí hay una
presentación sobre SQL / JSON.
Y aquí hay una
introducción a SQL / JSON.