Manejo de Documentos JSON

Clase 19 de 22 • Curso de PostgreSQL 2017

RODRIGO NOREÑA ARTEAGA

student•

Por si a alguien le interesa usar la tabla data:

-- Crear tabla  
CREATE TABLE data(  
    gender varchar(10),  
    height numeric,  
    weight numeric  
);  
--Poblarla  
INSERT INTO data   
SELECT  
  (SELECT CASE WHEN CEIL(RANDOM()*2) = 1 THEN 'Male' ELSE 'Female' END) as gender,  
  (SELECT 180 - ROUND((RANDOM()*20)::numeric, 2)) as height,  
  (SELECT 100 - ROUND((RANDOM()*45)::numeric,2)) as weight  
;

RODRIGO NOREÑA ARTEAGA

student•

--Poblarla (mediante un bloque anonimo de PL/pgSQL) 150 registros  
DO $$DECLARE r record;  
BEGIN   
  FOR i IN 1..150 LOOP  
    INSERT INTO data   
    SELECT  
      (SELECT CASE WHEN CEIL(RANDOM()*2) = 1 THEN 'Male' ELSE 'Female' END) as gender,  
      (SELECT 80 - ROUND((RANDOM()*15)::numeric, 2)) as height,  -- esto genera valores entre 65 y 80  
      (SELECT 100 - ROUND((RANDOM()*45)::numeric,2)) as weight  
    ;  
   END LOOP;  
END$$

Kevin Morales

student•

->> Obtiene el valor que tenemos en la llave
row_to_json: Convierte la fila en formato JSON
array_to_json Convierte el JSON en un array

Cesar David Ramírez Dimaté

student•

Realmente array_to_json es que convierte un array en un json. array TO json

-- En un solo registro, genera el json con el resultado del subquery.
SELECT array_to_json(array_agg(row_to_json(t))) FROM (SELECT gender, height FROM data LIMIT 3) AS t;

                                              array_to_json                                               
----------------------------------------------------------------------------------------------------------
 [{"gender":"Male","height":70.99},{"gender":"Female","height":69.82},{"gender":"Female","height":70.81}]
(1 row)

Juan Castillo

student•

que tan eficiente es convertir todo el resultado de un query a JSON, en términos de memoria y rapidez, que limites tiene esta funcionalidad?

Carlos Salazar

student•

Hola Juan. En realidad te puede funcionar para casos de Web Services como los Servicios RESTFull. Ya que convertirás la consulta a JSON para portabilidad ante cualquier lenguaje, si utilizas un framework robusto como Spring para Java la conversión no debe impactar a tu aplicación en rendimiento, rapidez o memoria.

Cesar David Ramírez Dimaté

student•

-- Genera una columna adicional de tipo text[] donde representa la informacion del campo 'tech' acompañado de un id.
SELECT p.id, string_to_array(string_agg(elem, ' , '), ',') AS list
FROM profiles p, json_array_elements_text(p.profile -> 'tech') elem
GROUP BY 1; -- "{"postgresql "," ruby "," elixir"}" , {"javascript "," nodejs"}

-- Genera una columna adicional de tipo text donde representa la informacion del campo 'name' acompañado de un id.
-- (->) Tipo json, (->>) Realiza el cast
SELECT id, profile ->> 'name' AS name FROM profiles;
-- Obtiene los registros json segun el valor de una llave.
SELECT * FROM profiles WHERE profile ->> 'name' = 'Mario';

-- Trae los tres primeros registros
SELECT * FROM data LIMIT 3;
-- Convierte cada registro con sus campos a formato json.
SELECT row_to_json(data) FROM data LIMIT 3;  -- "{"gender":"Male","height":70.99,"weight":64.48}"
-- Segun los campos que definamos, genera el registro en formato json. El inconveniente es que cambia el nombre de los campos por f1 y f2.
SELECT row_to_json(row(gender, height)) FROM data LIMIT 3;
-- Esta vez utiliza un subquery para generar el registro en formato json con los nombres de los campos.
SELECT row_to_json(t) FROM (SELECT gender, height FROM data LIMIT 3) AS t;
-- Ahora, en un solo registro, genera el json con el resultado del subquery.
SELECT array_to_json(array_agg(row_to_json(t))) FROM (SELECT gender, height FROM data LIMIT 3) AS t;  -- [{"gender":"Male","height":70.99},{"gender":"Female","height":69.82},{"gender":"Female","height":70.81}]

Cesar David Ramírez Dimaté

student•

Realmente era innecesario tocar temas de Ruby en este curso.

Sebastian Pérez Quintero

student•

Hola a tod@s, cómo estan? Me pueden ayudar con una consulta por favor?
En el video, cuando el profe por el minuto 11, escribe esta linea:

SELECT p.id, string_to_array(string_agg(elem, ' , '), ',') AS list
FROM profiles p, json_array_elements_text(p.profile -> 'tech') elem
GROUPBY 1;

por qué pone p.id, luego pone FROM profiles p… mi inquietud es que significa la p… de donde salio?

Y mi segunda inquietud es, en el resulta de esa query, una de las lineas es:

1 | {postgresql," ruby"," elixir"}

¿eso no esta como raro? ¿por qué el primer valor no lo pone entre comillas y los otros si?

Sebastian Pérez Quintero

student•

yo acá probando… si yo ejecuto esta query:

SELECT id, profile->>'tech' FROM profiles;

me imprime:

 id |               ?column?
----+--------------------------------------
  1 | ["postgresql", "JS", "MySQL", "PHP"]
  2 | ["JS", "NodeJS"]

¿qué diferencia hay de este al anterior que los muestra en { } y unos con comillas y otros sin?
Gracias

Diego Forero

Team Platzi•

para tu primera duda, la p es un alias para referirse a profile, so observas dice FROM profiles p, eso indica que p es el alias definido para profiles, esto se usa mucho para ahorrar tiempo de escritura en las consultas y sobre todo cuando tienes nombres de tablas que son muy largos.

En cuento a tu segunda duda es por la función que se usa de string to array ay que hae una serie de reemplazos para poder mostrar los elementos.

Edwin Solano RẶvẹn

student•

--Este sql me los lista

SELECT json_array_elements(profile->'tech') FROM profile;

Eduardo Rasgado Ruiz

student•

Es verdad, solo que a lo último es profiles; por la tabla profiles.
Así:
SELECT json_array_elements(profile->‘tech’) FROM profiles;

Buen aporte!

Cesar David Ramírez Dimaté

student•

Interesante. Es el mismo resultado que hacer SELECT json_array_elements(json_extract_path_text(profile, 'tech')::json) FROM profiles;

json_array_elements 
---------------------
 "postgresql"
 "ruby"
 "elixir"
 "javascript"
 "nodejs"
(5 rows)

Sergio Ivan Santacruz Madrigal

student•

Funciones PostgreSQL con JSON

https://www.postgresql.org/docs/9.3/static/functions-json.html

José Samuel Aguilar Morales

student•

Show how to get information from json field in postgresql db

Sergio Ivan Santacruz Madrigal

student•

Below a link to understand it better

http://www.postgresqltutorial.com/postgresql-json/

Nathaly Stefani Riaño Bejarano

student•

Una API (siglas de ‘Application Programming Interface’) es un conjunto de reglas (código) y especificaciones que las aplicaciones pueden seguir para comunicarse entre ellas: sirviendo de interfaz entre programas diferentes de la misma manera en que la interfaz de usuario facilita la interacción humano-software. Las API pueden servir para comunicarse con el sistema operativo (WinAPI), con bases de datos (DBMS) o con protocolos de comunicaciones (Jabber/XMPP).

Marco Elizalde

student•

Extraer de un arreglo por medio de una llave

Marco Elizalde

student•

Extraer el valor por medio de una llave

Marco Elizalde

student•

Consulta filtra por llave

Marco Elizalde

student•

Generar un documento JSON apartidar de información estructurada de una tabla

Marco Elizalde

student•

Generar un documento JSON apartir de ciertas columnas

Marco Elizalde

student•

Generar un documento JSON con todas las lineas del select

Manejo de Documentos JSON

Bienvenidos

Introducción a Postgresql

PostgreSQL: qué es

Lo nuevo en Postgresql

¿Por qué es PostgreSQL?

Entendiendo el Core de Postgresql

Archivos de configuración

Manejo De Conexiones

Preguntas y Respuestas

[Extra] Cómo instalar PostgreSQL en Linux, Windows y OS X (Mac).

Roles, Tipos de Datos, Rangos y JSON en Postgresql

Uso de roles en Postgresql

Creando una base de datos

Arquitectura de la información usando esquemas

Privilegios de usuario

Datos seriales

Cadenas de texto

Arreglos

Rangos

JSON

Preguntas y Respuestas

JSON y Postgis

Manejo de Documentos JSON

Postgres HStore

PostGis

Preguntas y Respuestas