PostgreSQL - query for array elements inside json data type

My application has a table column "params" that is a JSON data type.  As a side note, it's a JSON data type (vs JSONB) because the ordering of keys must stay consistent because this column is hashed and used as the key for caching.  The contents of the "params" column has object values that are strings, numbers, and arrays.

Environment: 

PostgreSQL 12.9

Take a params value that looks like:

{
    "filterId": "a1cef72a-9d84-4cfc-9690-9f4d772f446c",
    "name": "cherryshoetech",
    "active": true,
    "priority": 2,
    "areas": [{
            "type": "custom",
            "startedInside": true,
            "endedInside": false,
            "order": 0
        }
    ],
}

To query for a specific value in the "areas" array, make use of the json_array_elements function.  It will expand a JSON array to a set of JSON values.  Therefore, it will return one record for each element in the array.

The following will return a record for each json array element in analysis.params.areas:

select id, created_tstamp, cherryshoeareas
from cherryshoe.analysis analysis, json_array_elements(analysis.params#>'{areas}') cherryshoeareas;

Once you filter it down with the where clause, it will only return the record that satisfies that criteria. Below are examples for filtering by a string, integer, and boolean:

select id, created_tstamp, cherryshoeareas
from cherryshoe.analysis analysis, json_array_elements(analysis.params#>'{areas}') cherryshoeareas
where cherryshoeareas ->> 'type' = 'custom';

select id, created_tstamp, cherryshoeareas
from cherryshoe.analysis analysis, json_array_elements(analysis.params#>'{areas}') cherryshoeareas
where (cherryshoeareas ->> 'order')::integer = 0;

select id, created_tstamp, cherryshoeareas
from cherryshoe.analysis analysis, json_array_elements(analysis.params#>'{areas}') cherryshoeareas
where (cherryshoeareas ->> 'startedInside')::boolean is true
and (cherryshoeareas ->> 'endedInside')::boolean is false;

Helpful Articles:

https://www.postgresql.org/docs/12/datatype-json.html

https://stackoverflow.com/questions/22736742/query-for-array-elements-inside-json-type

https://www.postgresql.org/docs/12/functions-json.html

PostgreSQL - get position of second delimiter in string

There's many articles with examples of getting the first or last position of a delimiter in a string. Here's an example of getting the position of the second delimiter in a string. 

Environment: 

PostgreSQL 10.12

Get the position of the first delimiter ';' from the public.cherryshoe.description column:

select description, 
position(';' in description) from public.cherryshoe; 

Get the position of the second delimiter ';'.   Add char_length of each split_part - since we want the position, need two char_length's.  Add an additional length of 2 for each split_part function that is used to account for each of the two delimiters:

select description, 
(char_length(split_part(description, ';', 1))
+ char_length(split_part(description, ';', 2)) 
+ 2) from public.cherryshoe;

Get the position of the third delimiter ';'.  Add char_length of each split_part - since we want the position, need three char_length's.  Add an additional length of 3 for each split_part function that is used to account for each of the three delimiters:

select description, 
(char_length(split_part(description, ';', 1)) 
+ char_length(split_part(description, ';', 2)) 
+ char_length(split_part(description, ';', 3)) 
+ 3) from public.cherryshoe;

So on and so forth...

Creating PostgreSQL tables, views, columns, etc with case insensitivity

They key thing when defining postgreSQL tables, views, columns, etc with case insensitivity is to not put quotes around the names.  If you do, they will always be case sensitive with the need to put quotes around them to access them.  There is also an exception to this rule -  defining identifiers with lower case and quotes - it acts the same way as defining them without quotes.  This is because the default behavior for postgreSQL is to force identifiers to lowercase.  Below are examples for clarity.

This was verified on psql (PostgreSQL) 9.0.4.

Identifiers with quotes - the wrong way to do it:
CREATE TABLE "MYTABLE"
(
  "ID" numeric(9,0) NOT NULL,
  "DESCRIPTION" character varying(4000),
  CONSTRAINT "ID_PRIMARY_KEY" PRIMARY KEY ("ID")
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "MYTABLE" OWNER TO cherryshoe;

sql statement errors because of case sensitivity and quotes:
1
cherryshoe=> select * from mytable;
ERROR:  relation "mytable" does not exist
LINE 1: select * from mytable;

2
cherryshoe=> select * from MYTABLE;
ERROR:  relation "mytable" does not exist
LINE 1: select * from MYTABLE;

3
cherryshoe=> INSERT INTO mytable (id, description) VALUES (0, 'description');
ERROR:  relation "mytable" does not exist
LINE 1: INSERT INTO mytable (id, description) VALUES (0, 'descrip...

4
cherryshoe=> INSERT INTO MYTABLE (ID, DESCRIPTION) VALUES (1, 'description1');
ERROR:  relation "mytable" does not exist
LINE 1: INSERT INTO MYTABLE (ID, DESCRIPTION) VALUES (1, 'descrip...

5
cherryshoe=> INSERT INTO myTable (Id, Description) VALUES (2, 'description2');
ERROR:  relation "mytable" does not exist
LINE 1: INSERT INTO myTable (Id, Description) VALUES (2, 'descriptio...

sql statements work because case sensitivity and quotes:
6
cherryshoe=> select * from "MYTABLE";
 ID | DESCRIPTION
----+-------------
(0 rows)

7
cherryshoe=> INSERT INTO "MYTABLE" ("ID", "DESCRIPTION") VALUES (3, 'description3');
INSERT 0 1

Identifiers without quotes - three different ways to do it the right way:
a All Uppercase
CREATE TABLE MYTABLE
(
  ID numeric(9,0) NOT NULL,
  DESCRIPTION character varying(4000),
  CONSTRAINT ID_PRIMARY_KEY PRIMARY KEY (ID)
)
WITH (
  OIDS=FALSE
);

ALTER TABLE MYTABLE OWNER TO cherryshoe;

b All Lowercase
CREATE TABLE mytable
(
  id numeric(9,0) NOT NULL,
  description character varying(4000),
  CONSTRAINT id_primary_key PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

ALTER TABLE mytable OWNER TO cherryshoe;

c Camelcase
CREATE TABLE myTable
(
  Id numeric(9,0) NOT NULL,
  Description character varying(4000),
  CONSTRAINT Id_primary_key PRIMARY KEY (Id)
)
WITH (
  OIDS=FALSE
);

ALTER TABLE myTable OWNER TO cherryshoe;

All the sql statements that didn't work before, now work (The ones that didn't use case sensitivity and no quotes):
1
cherryshoe=> select * from mytable;
 id | description
----+-------------
(0 rows)

2
cherryshoe=> select * from MYTABLE;
 id | description
----+-------------
(0 rows)

3
cherryshoe=> select * from myTable;
 id | description
----+-------------

(0 rows)

4
cherryshoe=> INSERT INTO mytable (id, description) VALUES (0, 'description');
INSERT 0 1

5
cherryshoe=> INSERT INTO MYTABLE (ID, DESCRIPTION) VALUES (1, 'description1');
INSERT 0 1

6
cherryshoe=> INSERT INTO myTable (Id, Description) VALUES (2, 'description2');
INSERT 0 1

All the sql statements that worked before, now don't work (The ones that used case sensitivity and quotes):
7
cherryshoe=> select * from "MYTABLE";
ERROR:  relation "MYTABLE" does not exist
LINE 1: select * from "MYTABLE";
                      ^
8
cherryshoe=> INSERT INTO "MYTABLE" ("ID", "DESCRIPTION") VALUES (3, 'description3');
ERROR:  relation "MYTABLE" does not exist
LINE 1: INSERT INTO "MYTABLE" ("ID", "DESCRIPTION") VALUES (3, 'desc...

Here is the example for the exception to this rule -  defining identifiers with lower case and quotes - it acts the same way as defining them without quotes.  This is because the default behavior for postgreSQL is to force identifiers to lowercase.  

CREATE TABLE "mytable"
(
  "id" numeric(9,0) NOT NULL,
  "description" character varying(4000),
  CONSTRAINT "id_primary_key" PRIMARY KEY ("id")
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "mytable" OWNER TO cherryshoe;

Then all the same sql statements that did/did not work act the same way as defining a table with no quotes.

Takeaway - 
You probably want to choose all uppercase, all lowercase, or camelcase based on your project standards.  Or even easier is to pick all lowercase, so it fits with the postgreSQL default behavior.

Manual and Annotation based myBatis-Spring Configuration

Here are two essentially same examples of configuring myBatis-Spring with annotations or using manual configuration, using a custom maven modules.  You can look at the details on github (https://github.com/cherryshoe/cherryshoe-examples/tree/master/mybatis-spring), but the highlights are called out below.

Directory Structure

/*-custom-database     <-- Maven pom.xml

  /src

    /main/

      /java                   <-- Java code

        /com/

          /cherryshoe

            /database

                          /dao            <-- Dao logic

              /domain         <-- Business domain objects

              /persistence    <-- Mapper interfaces

      /resources              <-- Non java files

        /com

          /cherryshoe

            /database

              /persistence    <-- Mapper XML files

                /spring               <-- Spring files

    /test/

      /java                   <-- Java code

        /com/

          /cherryshoe

            /database

                          /dao            <-- Dao logic tests

      /resources              <-- Non java files

        /database             <-- Sql in memory files (H2)

        /spring               <-- Spring test files

Differences with the manual configuration method:
LogsDao.java

• No @Service for LogsDao class
• No @Autowired for LogsMapper
• Add in setter method for LogsMapper

  public void setVwDocManCtsMapper(VwDocManCtsMapper vwDocManCtsMapper) {
      this.vwDocManCtsMapper = vwDocManCtsMapper;
  }
  

spring-custom-database.xml and spring-custom-database-test.xml

• No <context:component-scan> to enable component scanning
• No <context:annotation-config> to enable use of autowiring
• No org.mybatis.spring.mapper.MapperScannerConfigurer to scan for mappers and let them be autowired
• Add in spring beans for the LogsMapper and LogsDao classes.  These can then also be used when importing this module into another maven module.

  <bean id="logsMapper" class="org.mybatis.spring.mapper.MapperFactoryBean">
    <property name="mapperInterface" value="com.cherryshoe.database.persistence.LogsMapper" />
    <property name="sqlSessionFactory" ref="sqlSessionFactory" />
  </bean>
  
  <bean id="logsDao" class="com.cherryshoe.database.dao.LogsDao" >
      <property name="logsMapper" ref="logsMapper" />
  </bean>
  

How to pass multiple parameters to a mybatis xml mapper element

Using mybatis mapper XML files with only one parameter to pass into SQL statements is straightforward.  For example, if you had a select statement that retrieved a record by an id, then you need to:

• Define an element in the xml mapper file.  i.e. the select element can receive a parameter type of "Long" with name of "logsId" with an id of "getRecordsByLogsId"
• Define a function in the mapper java file.  i.e. the function name is "getRecordsByLogsId" and takes an Integer parameter named "logsId"
• You'll notice that the id in the mapper xml file and the function name in the mapper java file match; as well as the parameter name defined in both.  
• The parameter notaton of #{} tells mybatis to make a prepared statement

Single Parameter XML Mapper:

  <select id="getRecordsByLogsId" parameterType="Long"resultMap="baseResultMap">

    SELECT

          <include refid="base_column_list" />

    FROM vw_cherryshoe

    WHERE logs_id = #{logsId}

  </select>

Mapper Java Class - single parameter - VwCherryShoeMapper.java:

VwCherryShoe getRecordByLogsId(Integer logsId);

I had to create a SQL statement to find all records between a date range for a timestamp with no timestamp column.  I needed  a way to pass multiple parameters to the xml mapper.  To do this you have to do in addition:

• In the xml mapper file, use a parameter type of "map".  This allows you to pass in multiple parameter names into the map
• In the mapper java file, use the @Param annotation for the function's parameters

Two parameters XML Mapper:

<select id="getRecordsBySentDateRange" parameterType="map"resultMap="baseResultMap">

    SELECT

          <include refid="base_column_list" />

    FROM vw_cherryshoe

    WHERE sent_date BETWEEN TO_TIMESTAMP(#{fromTimestamp}, 'DD/MM/YYYY HH24:MI:SS')

    AND TO_TIMESTAMP(#{toTimestamp}, 'DD/MM/YYYY HH24:MI:SS')

  </select>

Mapper Java Class - multiple parameter  - VwCherryShoeMapper.java
List<VwCherryShoe> getRecordsBySentDateRange(

@Param("fromTimestamp") String fromTimestamp, @Param("toTimestamp") String toTimestamp);

This worked with Postgresql 9.0.4 and Oracle Database 11g Enterprise Edition Release 11.2.0.2.0.

Accessing Alfresco's Spring datasource

In past articles I've discussed having the need to store custom application data in addition to Alfresco document and metadata storage in Alfresco's Two-Phase commit limitation, or a backup and restore strategy for Alfresco and a custom application.  For another recent project of mine, the custom application data that needed to be stored was so small that we decided to go ahead and store it inside the Alfresco database itself as an additional status table.

Note: A similar version of the example below were tested on:

• Alfresco 4.1.5 with Windows default Tomcat install with PostGreSQL 
• Alfresco 4.1.5 with JBoss 7.0.0 install with Oracle

High Level Steps:

1. Created a maven database module using mybatis for persistence.  This article helped immensely when setting this up.
• Included the CherryShoeStatusDao class, mybatis domain classes, mapper classes, and mapper xml files
• The key to accessing Alfresco's spring datasource is to reference it as "defaultDataSource" in the alfresco-custom-database spring configuration file's datasource bean.  This is because the alfresco datasource is defined in core-services-context.xml with that spring bean id.

  <!-- Alfresco's DataSource is obtained via a bean called "defaultDataSource" which is a org.apache.commons.dbcp.BasicDataSource, it's defined in WEB-INF/classes/alfresco/core-services-context.xml, simulating it here -->
  
  <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
  
  <property name="dataSource" ref="defaultDataSource" />
  </bean>
    

• Necessary dependencies included (versions were controlled by the parent pom so not shown here):

  <dependency>
   <groupId>org.mybatis</groupId>
   <artifactId>mybatis-spring</artifactId>
  </dependency>
  <dependency>
   <groupId>log4j</groupId>
   <artifactId>log4j</artifactId>
  </dependency>
  <dependency>
   <groupId>cglib</groupId>
   <artifactId>cglib</artifactId>
  </dependency>
  <dependency>
   <groupId>com.google.guava</groupId>
   <artifactId>guava</artifactId>
  </dependency>
    

2. Add the database module as a module dependency to the alfresco amp module pom:
   

       <dependency>
            <groupId>com.cherryshoe</groupId>
           <artifactId>alfresco-custom-database</artifactId>
       </dependency>
   

3. Import the alfresco-custom-database.xml spring file to module-context.xml so it will be recognized by alfresco. The classpath should be from the root of the jar file created (/spring/spring-alfresco-custom-database.xml). Import it before any other imports that depend on it.
   

   <!--  alfresco-custom-database spring config file -->
   <import resource="classpath:/spring/spring-alfresco-custom-database.xml" />
   

   ... other imports
   
4. cherryShoeStatusDao will already be available as a spring bean after step 3 is done because of component scanning in the custom database module spring configuration file.  It can be referenced from other beans in alfresco's service-context.xml custom spring config file.  i.e. a CustomAlfrescoService can now access CherryShoeStatusDao to insert and update Status table values.
   

          
   <bean id="CustomAlfrescoService" class="com.cherryshoe.services.impl.CustomAlfrescoServiceImpl" >
       <property name="cherryShoeStatusDao" ref="cherryShoeStatusDao" />
   </bean>

   
   

Reference Only
Detailed Files: