This post shows you how to parse string to json and then back again.
String to Json
var doc = null; try { doc = JSON.parse(value); } catch(ex) { //handle exception }
Json to string
JSON.stringify(value));
A place for tutorials on programming and other such works.
This post shows you how to parse string to json and then back again.
var doc = null; try { doc = JSON.parse(value); } catch(ex) { //handle exception }
JSON.stringify(value));
When you want to work with JSON the package of choice is Jackson. I find it so useful and easy to use.
Maven:
<dependency> <groupId>com.fasterxml.jackson.core</groupId> <artifactId>jackson-core</artifactId> <version>2.7.1</version> </dependency> <dependency> <groupId>com.fasterxml.jackson.core</groupId> <artifactId>jackson-annotations</artifactId> <version>2.7.1</version> </dependency> <dependency> <groupId>com.fasterxml.jackson.core</groupId> <artifactId>jackson-databind</artifactId> <version>2.7.1</version> </dependency>
There are so many things that we can do with Jackson. It’s actually very exciting.
Let’s start with converting a json string to ObjectNode.
import com.fasterxml.jackson.databind.ObjectMapper; import com.fasterxml.jackson.databind.node.ObjectNode; //We need an object mapper to help with the conversion final ObjectMapper mapper = new ObjectMapper(); //Next we read the json string into ObjectNode final ObjectNode node = (ObjectNode)mapper.readTree(jsonString); //You can check if the objectnode has a key by node.has("my_key") //You can get the value by node.get("my_key") //To get the value as a string using .asText() at the end of the get //To get as int .asInt() //To get as boolean .asBoolean() //To get as double .asDouble() //To get as Long .asLong()
Next let’s convert a json string to JsonNode
import com.fasterxml.jackson.databind.JsonNode; import com.fasterxml.jackson.databind.ObjectMapper; //You need an object mapper to help with the conversion final ObjectMapper mapper = new ObjectMapper(); //Next we read the json string into a jsonnode final JsonNode node = mapper.readTree(jsonString); //We can if you want to switch it to an object node fairly easily. final ObjectNode objNode = (ObjectNode)node; //Put value in the object node objNode.put("my_key", 1); //You can also set json node objNode.set("my_key", myJsonNode);
You can also create a new object node by using JsonNodeFactory. See below.
import com.fasterxml.jackson.databind.node.JsonNodeFactory; import com.fasterxml.jackson.databind.node.ObjectNode; //If you want to create an instance of ObjectNode final ObjectNode objNode = JsonNodeFactory.instance.objectNode();
If you want to work with json array’s it’s also pretty straight forward. Again we work with JsonNodeFactory to declare it out.
import com.fasterxml.jackson.databind.node.ArrayNode; import com.fasterxml.jackson.databind.node.JsonNodeFactory; private ArrayNode arrNode = JsonNodeFactory.instance.arrayNode(); //We can then add to it arrNode.add();
Jackson also has generators which allow us to create json on the fly.
import com.fasterxml.jackson.core.JsonFactory; import com.fasterxml.jackson.core.JsonGenerator; import com.fasterxml.jackson.core.JsonProcessingException; //It throws a "JsonProcessingException' final JsonFactory jfactory = new JsonFactory(); ByteArrayOutputStream out=new ByteArrayOutputStream(); try (final JsonGenerator jg = jfactory.createGenerator(out, JsonEncoding.UTF8)) { jg.setCodec(objectMapper); jg.writeStartObject(); jg.writeFieldName("my_key"); jg.writeString("hi"); jg.writeFieldName("next_key"); jg.writeObject(my_obj); jg.writeFieldName("third_key"); jg.writeTree(my_obj_node); jg.writeEndObject(); }
Jackson JsonViews are really cool in my opinion. We can have an object that has multiple properties and we can set a view to each one of these properties. Which means when we serialize the object we will only get those fields that are associated to that view and vise versa on the deserialize.
import com.fasterxml.jackson.annotation.JsonCreator; import com.fasterxml.jackson.annotation.JsonProperty; import com.fasterxml.jackson.annotation.JsonView; //You can however many views we want to work with in our objects. They must all be classes. //If you extend a class then it will contain all properties of that view and the view it extends. public class MyClassViews { public static class ViewA {} public static class ViewB extends ViewA {} } //Let's create an object for our serialization and deserialization public class MyClass { private final int id; private final String value; //This constructor is what the deserializer would use. @JsonCreator public MyClass(@JsonProperty("id") int id, @JsonProperty("value") String value) { } @JsonView(MyClassViews.ViewA.class) @JsonProperty("id") //You don't have to put the name you could leave it as @JsonProperty public int getId() { return id; } @JsonView(MyClassViews.ViewB.class) @JsonProperty("value") public String getValue() { return value; } }
I didn’t come up with this next part but it’s so awesome that I wanted to include it. Let’s say you had an object that was populated not using any views but the object does contain views that will eventually pass up to a UI but you only want to pass using a specific view. Then you can do the following.
import javax.ws.rs.core.StreamingOutput; import com.fasterxml.jackson.databind.ObjectMapper; final StreamingOutput jsonStream = new StreamingOutput() { @Override public void write(OutputStream out) throws IOException { final ObjectMapper mapper = new ObjectMapper(); mapper.writerWithView(MyClassViews.ViewB.class).writeValue(out, MyClass); } };
If you want to write the object to string using a view. All you need to do is
final String jsonString = new ObjectMapper().writerWithView(ViewB).writeValueAsString(MyClass);
If you then want to convert our new jsonString to the object using the view do the following:
new ObjectMapper().readerWithView(ViewB.class).forType(MyClass.class).readValue(jsonString);
If you want to convert a json string to object.
new ObjectMapper().readValue(myJsonString, MyClass.class)
When you deal with json data type there is the option of using json_typeof(value). However this doesn’t always give you what you expect. For example a date field renders as text. To get around this I have the following two functions. Feel free to use them or suggest alternatives.
Test Value is Date:
create or replace function is_date(s varchar) returns boolean as $$ begin perform s::date; return true; exception when others then return false; end; $$ language plpgsql;
Test Json Value for Type:
CREATE OR REPLACE FUNCTION get_json_type(s json) returns text as $$ DECLARE val_type text; BEGIN IF (SELECT public.is_date(s::text)) THEN val_type := 'date'; ELSEIF (json_typeof(s) = 'number') THEN val_type := 'double precision'; ELSEIF (json_typeof(s) = 'string') THEN val_type := 'text'; ELSEIF (json_typeof(s) = 'boolean') THEN val_type := 'boolean::integer'; ELSE val_type := json_typeof(s); END IF; return val_type; END; $$ language plpgsql;
Here are some misc things you can do in postgres 9.4.
Print to Console:
raise notice 'VALUE: %', value;
Json Build Object:
json_build_object('name', value, 'name2': value2)
Json Array:
json_agg(data)
Extract Month:
You can also extract year, day, etc.
extract(month from date_column)
Json Querying:
You can extract a field from a json field. You can do:
rec->>'id' rec#>'{field, sub_field,value}' rec->'object'
Row To Json:
SELECT row_to_json(t) FROM mytable t
Update With FROM:
UPDATE mytable l SET old_val_id=sub.new_val_id FROM ( SELECT l2.id, s.id as new_val_id FROM mytable l2 INNER JOIN mysupertable s ON s.id=l2.old_val_id ) sub WHERE sub.id=l.id;
Inline Script:
DO $do$ DECLARE id integer; BEGIN END $do$ LANGUAGE plpgsql;
If:
IF CONDITION THEN END IF;
Let’s say you have a json field and in that field you have a field which tells you what key you should select for certain data. It could be customizable from the user entering data. To dynamically select that field you can do the below.
jsonField->((to_json((jsonField->'key'->'sub_key'))#>>'{}')::text)
Upsert:
Sometimes we want to perform update if the record exists or an insert if it doesn’t. Most of the time we write a function that deals with this on a record by record basis. But what if we want to do a batch update or insert. What do we do then. We perform an upsert. See below. We write the update and return the result in the remainder of the query we check if upsert gave us anything. If it didn’t we perform the insert.
WITH upsert AS (UPDATE MyTable mt SET column_name = sub.new_value FROM (SELECT mot.id, mot.new_value FROM MyOtherTable mot ) sub WHERE sub.id=mt.related_id RETURNING *) INSERT INTO MyTable (id, column_name, related_id) SELECT ?, ?, ? WHERE NOT EXISTS (SELECT * FROM upsert)
Regex Substring:
There are different ways of using regex. This is one way.
substring(column_name from '([0-9]{4}-[0-9]{2}-[0-9]{2})')
PGSQL Loop:
This is one way to loop using pgsql.
DO $do$ DECLARE rec RECORD; BEGIN FOR rec IN SELECT * FROM MyTable LOOP --We can then use rec like rec.colum_name END LOOP; END $do$ LANGUAGE plpgsql;
Milliseconds to timestamp:
This will return 2017-08-17 21:26:04
select to_timestamp(1503005165);
You must be logged in to post a comment.