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

• 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

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

SELECT '{"sensor_id": "1234", "reading": {"temperature": 22.5, "unit": "C"}}'::jsonb -> 'reading' ->> 'unit' AS sensor_reading;

• 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

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.'