Diferencias

Muestra las diferencias entre dos versiones de la página.

--- wiki2:postgresql [2019/01/29 17:17]
alfred [Basics]
+++ wiki2:postgresql [2020/05/09 09:25] (actual)
@@ Línea 74: / Línea 74: @@
 </code>
+===== JSONB =====
+You can create a table with json data:
+<code>
+create table macarrodes.bookmarks (
+  id serial primary key,
+  title text,
+  meta jsonb
+);
+</code>
+==== Query ====
+Simple query showing a field:
+<code>
+select distinct meta->'tags' from macarrodes.macarrodes.bookmarks;
+</code>
+Query by field:
+<code>
+SELECT * FROM users WHERE metadata @> '{"country": "Peru"}';
+SELECT * FROM users WHERE metadata->>'country' = 'Peru';
+</code>
+Query nested values:
+<code>
+SELECT * FROM users WHERE metadata->'company'->>'name' = "Mozilla";
+SELECT * FROM users WHERE metadata @> '{"company":{"name": "Mozilla"}}';
+</code>
+Query if exists:
+<code>
+SELECT * FROM users WHERE metadata->>'country' IS NOT NULL;
+</code>
+Query in array:
+<code>
+SELECT * FROM users WHERE metadata @> '{"companies": ["Mozilla"]}';
+SELECT * FROM users WHERE metadata->>'countries' IN ('Chad', 'Japan');
+</code>
+Remove an attribute:
+<code>
+UPDATE users SET metadata = metadata - 'country';
+</code>
 ===== Funciones =====

Programming