MAG postgresql jsonb cheatsheet

mag_cheatsheet.sql

-- Selecting nested elements (e.g. author names)
select author -> 'name' as author_name 
from microsoft_academic_graph 
cross join jsonb_array_elements(microsoft_academic_graph.paper -> 'authors') author 
limit 1;

-- Finding specific papers by author
select paper->'title', paper->'authors' 
from microsoft_academic_graph 
where paper->'authors' @> '[{"name":"John Smith"}]' 
limit 1;

-- Finding indexed items. Note, my indexes are:
--     "doi_index" btree ((((paper ->> 'doi'::text))::character varying(255)))
--     "lang_index" btree ((paper ->> 'lang'::text))
--     "papers_authors_name_gin_idx" gin ((paper -> 'authors'::text) jsonb_path_ops)
--     "title_index" btree ((((paper ->> 'title'::text))::character varying(300)))
--     "year_index" btree (((paper ->> 'year'::text)::integer))
select paper->>'title' 
from microsoft_academic_graph 
where ((((paper ->> 'doi'::text))::character varying(255))) = '10.1088/1748-0221/11/07/P07010' 
limit 1;