====== PostgreSQL JSON ======

  * [[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>