JSONB - sgml/signature GitHub Wiki

• Get length of a column bar in table foo JSONB array:

    SELECT jsonb_array_length(bar)
    FROM foo
    where baz = 'bop'
  

• Find a string in a JSONB array:

    SELECT 1
    FROM jsonb_each_text(tags) AS j(key, value)
    WHERE j.value ~ 'already'
  

• Find/Replace JSON Text

    UPDATE foo 
    SET jsonb_column = replace(jsonb_column::text, 'CALIFA', 'Khalifa')::jsonb
    WHERE foo_uuid = '7a0be34a-9095-4a4e-b07b-337a090342c7'
  

References

• https://devhints.io/postgresql-json
• https://gist.github.com/DaddyMoe/076a47c80a68ffb85110702d48195eb2
• https://bender.io/2013/09/22/returning-hierarchical-data-in-a-single-sql-query/
• https://www.postgresql.org/message-id/54C03B86.80604%40dunslane.net
• https://stackoverflow.com/questions/71484213/postgresql-open-jsonb-column-with-slashes
• https://linq2db.github.io/articles/sql/CTE.html
• http://tatsuo-ishii.github.io/postgresql/current/acronyms.html
• https://web.mit.edu/cygwin/cygwin_v1.3.2/usr/doc/postgresql-7.1.2/html/rules.html
• http://web.mit.edu/11.521/www/sqlnotes/sqlhelp.html
• http://aturing.umcs.maine.edu/~sudarshan.chawathe/200909/cos480/pgsql/doc/html/plpgsql-statements.html