Diferencias

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

--- informatica:base_de_datos:postgresql:postgresql_json [2026/01/24 22:25] – creado admin
+++ informatica:base_de_datos:postgresql:postgresql_json [2026/02/12 01:10] (actual) – [Probar si un JSON valido] admin
@@ Línea 2: / Línea 2: @@
   * [[https://stackoverflow.com/questions/72312526/json-arrays-of-objects-postgresql-table-format]]
+  * [[https://www.tigerdata.com/learn/how-to-query-jsonb-in-postgresql]]
+  * [[https://jsoneditoronline.org/|editor online json]]
+  * [[https://www.crunchydata.com/blog/easily-convert-json-into-columns-and-rows-with-json_table]]
+  * [[https://gitlab.syncad.com/hive/pgsql-http/-/blob/master/sql/http.sql]]
+  * [[https://www.postgresql.org/docs/9.5/functions-json.html]]
+  * [[https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_each]]
+  * [[https://www.postgresql.org/docs/current/functions-json.html]]
+<code postgresql>
+select * from
+json_to_recordset(
+  '{"a":{"b":[{"f1":2,"f2":4},{"f1":3,"f2":6}]}}'::json->'a'->'b' --inner table b
+)
+as x("f1" int, "f2" int); --fields from table b
+</code>
+<code postgresql>
+SELECT '{"sensor_id": "1234", "reading": {"temperature": 22.5, "unit": "C"}}'::jsonb -> 'reading' ->> 'unit' AS sensor_reading;
+</code>
+===== Probar si un JSON es valido =====
+  * [[https://stackoverflow.com/questions/30187554/how-to-verify-a-string-is-valid-json-in-postgresql]]
+    * [[https://stackoverflow.com/questions/30187554/how-to-verify-a-string-is-valid-json-in-postgresql/30187851#30187851]]
+<code postgresql>
+CREATE OR REPLACE FUNCTION f_is_json(_txt text)
+  RETURNS bool
+  LANGUAGE plpgsql IMMUTABLE STRICT AS
+$func$
+BEGIN
+   RETURN _txt::json IS NOT NULL;
+EXCEPTION
+   WHEN SQLSTATE '22P02' THEN  -- invalid_text_representation
+      RETURN false;
+END
+$func$;
+COMMENT ON FUNCTION f_is_json(text) IS 'Test if input text is valid JSON.
+Returns true, false, or NULL on NULL input.'
+</code>