I've been searching for PostgreSQL NoSQL and found the Key-Value Stores concept in some HSTORE publications. I also looked into the integration between PostgreSQL and the Spring framework and found the following code example:
package com.jamesward.model;
import net.backtothefront.HstoreUserType;
import org.hibernate.annotations.Type;
import org.hibernate.annotations.TypeDef;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import java.util.HashMap;
import java.util.Map;
@Entity
@TypeDef(name = "hstore", typeClass = HstoreUserType.class)
public class Contact {
@Id
@GeneratedValue
public Integer id;
@Column(nullable = false)
public String name;
@Type(type = "hstore")
@Column(columnDefinition = "hstore")
public Map<String, String> contactMethods = new HashMap<String, String>();
}
As you can see in this entity there are 3 (three) fields id
Integer, name
String type and contactMethods
which is the field where JSON will be stored.
And searching in an EnterpriseDB PDF I found the following command for creating a table with a single field only named json_data
which will store JSONB type values.
CREATE TABLE json_data (data JSONB);
In addition to a SELECT like this (besides INSERT and other actions using this entity that can be found in the references published at the end of the question):
SELECT DISTINCT
data->>'name' AS "Product Name",
data->>'price' AS "price"
FROM json_data
WHERE data->>'brand' = 'ACME';
And what I would like to know is based on these questions:
References:
EnterpriseDB - Using the NoSQL Capabilities in Postgres
James Ward - NoSQL Inside SQL with Java, Spring, Hibernate, and PostgreSQL
James Ward - NoSQL Inside SQL with Java, Spring, Hibernate, and PostgreSQL (GitHub repository)