Here is the script for sample Json and XML 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; /**************************** basic xml queries ***********************/ --create a table with an xml data type Create table xmlTest ( xmltestId serial primary key, test xml ); --insert some xml Insert into xmlTest(test) values('<person><name>Joe Smith</name><email>js@gmail.com</email><age>34</age></person>'), ('<person><name>Kelly Jones</name><email>kj@gmail.com</email><age>24</age></person>'), ('<person><name>Lila Bard</name><email>lb@gmail.com</email><age>25</age></person>'); Select * from xmltest; Select xmlelement(name name, test) from xmltest; --return emails Select xpath('//email/text()', test) from xmltest; --return ages Select xpath('//age/text()', test)from xmltest; --put xml tags around query results Select xmlforest(personlastname, personemail) from person; --output a table as xmlf= fragment Select Table_to_xml('granttype',True, True, ''); --output query results as xml fragment Select query_to_xml('Select personfirstname, personlastname, positionname from person join employee using (personkey) join employeeposition using (employeekey) join jobposition using (positionkey)' ,True, True, '') ; Select table_to_xmlschema('granttype',True, True, '');
ReplyDeleteHi there everyone, I have come across almost all the web development sites. However, your website is far better than other in form of content, tools and easiness of using website. Since I am a developer, I am always looking forward to make people aware of web development tools. I can help you people out by introducing you to range of json tools. Here is the link jsononline
ReplyDeleteHi there everyone. There is this incredible gadget that would surely amaze you. This heat press machine with a variety of features can do wonders. There is a separate heat press machine for T-shirts and larger items. And a 5 in 1 heat press for t-shirts, mugs, caps, mats and garments. And another 6 in 1 heat press for bags, mugs, T-shirts, caps, tiles, Plates and mouse- pads. Get yourself this amazing gadget and make your belongings look chick. Here is the link of the site Heat Press Reviews