8, PostgreSQL - rextester

Language: Layout:

--PostgreSQL 9.6
--'\\' is a delimiter

select version() as postgresql_version;

-- in order to analyse some json data:
-- ==================================
-- should I start coding, by use of substr, pos and other peeks and pokes 
-- or
-- handle this by using sql json operators 
select 
  jsn.jsondata ->> 'kind' as kind,
  jsn.jsondata ->> 'totalItems' as totalItems,
  jsn.jsondata ->> 'items' as ItemsArray,
  jsn.jsondata #>> '{items,1}' as scnd_ArrayItem,
  jsn.jsondata #>> '{items,0}' as frst_ArrayItem,
  jsn.jsondata #>> '{items,3}' as third_ArrayItem,
  jsn.jsondata #>> '{items,4}' as fiveth_nonExistingArrayItem ,         
  jsn.jsondata #> '{industryIdentifiers,0}'->>'identifier' as ISBN10
  from ( select '{
				     "kind": "books#volumes",
				     "totalItems": 3,
	 	 		     "items": ["a", "b", "c", "d"],
	 			     "industryIdentifiers": [{
				             "type": "ISBN_10",
				             "identifier": "1696290007"
				         }, {
				             "type": "ISBN_13",
				             "identifier": "9781696290005"
				         }
				     ]
				 }    					
                '::json as jsondata   -- example data taken/ extracted/ inspired by https://schema.org/Book  aka google books schema
                                      -- json type, aliasing, subselect only required for use with plain json.
                                      -- this is unneccessary when json data comes from json typed column
       ) jsn

-- This single sql statement only shows json data handling in example, 
-- but it could also be seen as an example of scaling data processing.
-- Use of JSONB Type instead of plain JSON would be the obvious keyword then in conjunction with postgresql and scaling.


absolute service time: 0,49 sec