Here is the code I used in the video on Basic JSON queries.
/********************* basic JSON queries *********************/ --create a table with Json as a datatype Create table JsonTest ( testID serial primary key, test Json ); --insert some records Insert into JsonTest(test) Values('{"testname" : "testOne", "testnumber" : 123, "testdate" :"2019-05-02"}'), ('{"testname" : "testtwo", "testnumber" : 234, "testdate" :"2019-05-08"}'), ('{"testname" : "testthree", "testnumber" : 345, "testdate" :"2019-05-12"}'); --query the value of a field Select test ->>'testname' as "name" from jsontest; --creates an array of sorts putting each element on its own row Select Json_each(test) from Jsontest; --returns the keys in the Json record Select Distinct Json_object_keys(test) from jsontest; --gets tne count of tests Select count(cast (test ->> 'testnumber' as integer)) from Jsontest; --returns the results of a query as Json --double click the results to get a little dialog box --copy and paste to a text editor to see the full results with j as ( Select personfirstname, personlastname, positionname from person join employee using (personkey) join employeeposition using (employeekey) join jobposition using (positionkey) ) Select json_agg(j) from j;
No comments:
Post a Comment