Parsing jsonb objects in Postgres

Check out this link for the official postgres json functions and operators

9.16. JSON Functions and Operators

CREATE TABLE emp (  
  emp_id int NOT NULL,
  data jsonb
);

INSERT INTO emp VALUES 
(1, '{"name": "John", "hobbies": ["Movies", "Football", "Hiking"]}');  

INSERT INTO emp VALUES 
(2, '{"name": "Marc", "hobbies": ["Gaming", "Movies", "Music"]}');

SELECT * FROM emp;

select data -> 'name' as name from emp;

select
	*
from emp
where data->'name'='"Marc"'

select
  data->'hobbies' as hobbies
from emp;

select
  jsonb_array_elements_text(data->'hobbies') AS hobbies
from emp;

select
  distinct(jsonb_array_elements_text(data->'hobbies')) AS distinct_hobbies
from emp;

select
  emp_id,
  jsonb_array_elements_text(data->'hobbies') AS hobbies
from emp;

select
  emp_id,
  data
from emp
where data->'hobbies'@>'["Gaming"]'::jsonb;

Pivoting in Postgres

CREATE OR REPLACE FUNCTION sq.dev.unnest(anyarray)
  RETURNS SETOF anyelement AS
$BODY$
SELECT $1[i]
FROM   generate_series(array_lower($1,1), array_upper($1,1)) i
$BODY$
  LANGUAGE sql IMMUTABLE;

SELECT
   unnest(array['column_1','column_2']) AS metric,
   unnest(array[column_1,column_2]) AS metric_value
FROM sq.dev.test
ORDER BY metric;