Java: Jackson Json

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)

Postgres: Check Data Type

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;

 

Postgres: Misc

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);