clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2335776 fiddles created (27471 in the last week).

DROP TABLE IF EXISTS t; CREATE TABLE t (j jsonb); INSERT INTO t (j) VALUES ( '[{ "token": "a978f9793a594d11860e9cf7962996d7" ,"name": "vehicle.moving" ,"description": "Vehicle is moving." }, { "token": "95e38bf08c2842f39f2d64ba8152b265" ,"name": "vehicle.stopped" ,"description": "Vehicle, with a driver/rider in/on it, is currently stationary but has an intent to move." }]' ); TABLE t;
1 rows affected
j
[{"name": "vehicle.moving", "token": "a978f9793a594d11860e9cf7962996d7", "description": "Vehicle is moving."}, {"name": "vehicle.stopped", "token": "95e38bf08c2842f39f2d64ba8152b265", "description": "Vehicle, with a driver/rider in/on it, is currently stationary but has an intent to move."}]
 hidden batch(es)


-- extract records from array SELECT jsonb_array_elements(j::jsonb) FROM t;
jsonb_array_elements
{"name": "vehicle.moving", "token": "a978f9793a594d11860e9cf7962996d7", "description": "Vehicle is moving."}
{"name": "vehicle.stopped", "token": "95e38bf08c2842f39f2d64ba8152b265", "description": "Vehicle, with a driver/rider in/on it, is currently stationary but has an intent to move."}
 hidden batch(es)


-- in two steps within a query -- extract json records from array -- and create table records SELECT vr.* FROM t ,jsonb_array_elements(j::jsonb) AS tt(recs) ,jsonb_to_record(tt.recs::jsonb) AS vr(token text, name text, description text);
token name description
a978f9793a594d11860e9cf7962996d7 vehicle.moving Vehicle is moving.
95e38bf08c2842f39f2d64ba8152b265 vehicle.stopped Vehicle, with a driver/rider in/on it, is currently stationary but has an intent to move.
 hidden batch(es)


-- same thing with jsonb_to_recordset() SELECT v.* FROM t ,jsonb_to_recordset(t.j::jsonb) AS v(token text, name text, description text);
token name description
a978f9793a594d11860e9cf7962996d7 vehicle.moving Vehicle is moving.
95e38bf08c2842f39f2d64ba8152b265 vehicle.stopped Vehicle, with a driver/rider in/on it, is currently stationary but has an intent to move.
 hidden batch(es)


-- store data in a table CREATE TABLE attribute ( token text ,name text ,description text ); INSERT INTO attribute SELECT v.* FROM t ,jsonb_to_recordset(t.j::jsonb) AS v(token text, name text, description text); TABLE attribute;
2 rows affected
token name description
a978f9793a594d11860e9cf7962996d7 vehicle.moving Vehicle is moving.
95e38bf08c2842f39f2d64ba8152b265 vehicle.stopped Vehicle, with a driver/rider in/on it, is currently stationary but has an intent to move.
 hidden batch(es)