2024-01-27

JSONB のキーと他のキーの複合キー（by PostgreSQL）

SQL

先日書いた
JSONB の primary key を作る ( by PosetgreSQL ) - Oboe吹きプログラマの黙示録
これでは、目的の主キーとして、任意の列とJSONB型のJSON内の値のキーとする場合の
CONSTRAINT を２つ書けない制約でUPSERT文 (ON CONFLICT～DO UPDATE）を思うように書けない。
そこで、PRIMARY KEY 制約で指定するのではなく、
NOT NULL制約と、UNIQUE インデックス制約でON CONFLICT～DO UPDATE を書けるようにする。

DROP TABLE jsample;

CREATE TABLE jsample (
    id INTEGER NOT NULL,
    title VARCHAR(60) NOT NULL,
    jdoc jsonb NOT NULL,
    CONSTRAINT jsample_jdockey_not_null CHECK ((((jdoc->'item'::text)->>'id'::text) IS NOT NULL))
);
CREATE UNIQUE INDEX jsample_ux1 ON public.jsample USING btree (id, (((jdoc -> 'item'::text) ->> 'id'::text)));
CREATE UNIQUE INDEX jsample_ux2 ON public.jsample USING btree ((((jdoc -> 'item'::text) ->> 'id'::text)));

これで、アップサートは以下のように実行できる。
btree のインデックスでユニークインデックスを作成することがミソ

insert INTO jsample (id, title, jdoc)
VALUES(11, 'test1', jsonb_build_object('A', 11, 'item',jsonb_build_object('id', 'idno_11')) )
ON CONFLICT(id, ((jdoc->'item'::text)->>'id'::text) ) 
DO UPDATE SET title = 'testup123'

または、

insert INTO jsample (id, title, jdoc)
VALUES(11, 'test1', jsonb_build_object('A', 11, 'item',jsonb_build_object('id', 'idno_11')) )
ON CONFLICT(id, ((jdoc->'item'::text)->>'id'::text) ) 
DO UPDATE SET 
title = 'testup123',
jdoc = '{"A": 123,  "item": {"id": "idno_11"}}'::jsonb

DO UPDATE で、JSONB_SET関数で更新を書く時に、ついテーブル名を指定しないと
SQLエラー [42702]: 列参照が曖昧となるので、
DO UPDATE で実行する時は、 JSONB_SET( テーブル名.JSONB型の列名 , path , value , [ boolean ] )
とすること

2024-01-22

JSONB の primary key を作る ( by PosetgreSQL )

SQL

単純に CREATE TABLE の PRIMARY KEY で指定してもエラーになる。

CREATE TABLE public.jsample (
      id int4 NOT NULL,
      title varchar(60) NOT NULL,
      jdoc jsonb not NULL,
      PRIMARY KEY (id, jdoc->'item'->>'id')
);

SQLエラー [42601]: ERROR: "->"またはその近辺で構文エラー
PRIMARY KEY 複合の場合、一旦、JSONB型の列を指定しないで TABLE CREATE する。

CREATE TABLE public.jsample (
	id int4 NOT NULL,
	title varchar(60) NOT NULL,
	jdoc jsonb not NULL,
	PRIMARY KEY (id)
);

UNIQUE インデックスと、NOT NULL制約を JSONパスに対して指定する。

DROP INDEX IF EXISTS jsample_ix1;
CREATE UNIQUE INDEX jsample_ix1 ON jsample ((jdoc->'item'->>'id'));
ALTER TABLE jsample ADD CONSTRAINT jsample_jdockey_not_null CHECK ((jdoc->'item'->>'id') IS NOT NULL);

”item" の下、"id" のテキストが、PrimaryKey 指定の id と一緒に、複合の
Primary Key になる。

でも、このような複合による制約の場合、UPSERT を書いた時に、
ON CONFLICT だけで対象インデックスを省略すれば、存在する全ての制約を自動でチェックして　
DO NOTHING ならうまくいくが、DO UPDATE では、
　 SQLエラー [42601]: ERROR: ON CONFLICT DO UPDATE は推定指定または制約名を必要
になる。

ON CONFLICT(id, ((jdoc->'item'::text)->>'id'::text) )

では、SQLエラー [42P10]: ERROR: ON CONFLICT 指定に合致するユニーク制約または排除制約がありません
になってしまう。
ON CONFLICT ON CONSTRAINT ｛制約名｝
制約名を２つ、カンマで区切って指定なんてできないので、
id と JSONB 列内のキーの２個の制約を作れれば、do update も書けるようになる。

上のテーブルで、id が PrimaryKey でなければ、

ON CONFLICT(((jdoc->'item'::text)->>'id'::text) )
DO UPDATE SET

は、成立する。

続き、、、
https://oboe2uran.hatenablog.com/entry/2024/01/27/222041

2024-01-19

json_set／jsonb_set で NULL をセットする時の注意

SQL

PostgreSQL でのことである。
あるテーブルの JSONB型に、以下のようなJSON値があるとする。

{
  "a":{
     "a1": "Apple",
     "a2": "Lemon"
  }
}

このJSONの"a1" を null にする場合、シングルクォートで括った
文字列　' null' 指定した以下なら成功する。

UPDATE jbooks SET
jdata = jsonb_set(jdata, '{a,a1}', 'null')
WHERE id = 4

しかし、null をシングルクォートで括らずに

jdata = jsonb_set(jdata, '{a,a1}', null)

としてしまうと、JSONB型のデータ列に null が入ってしまいパスで
指定した値だけを null にできない。

PostgreSQL バージョン13から、jsonb_set_lax と関数が拡張されており、
　jsonb_set(target jsonb, path text, new_value jsonb[, create_missing boolean])
の create_missing boolean を省略せずに

jsonb_set_lax( target jsonb, path text, new_value jsonb
　　　　　　　　　　　 [, create_if_missing boolean [, null_value_treatment text ]] )

null_value_treatment を指定することで new_value が null の時の振る舞いを指定する。

null_value_treatment は、

raise_exception	new_value が null ならエラーになり、22004：NULL VALUE NOT ALLOWED になる
use_json_null	new_value が null なら null がセットされる
delete_key	path で設定された JSONはキーと値が削除される
return_target	new_value が null なら変更されない、null でなければそのまま反映される。

だから、

jdata = jsonb_set_lax(jdata, '{a,a1}', null, true, 'use_json_null')

とすれば、期待どおり

{
  "a":{
     "a1": null,
     "a2": "Lemon"
  }
}

になる。
oboe2uran.hatenablog.com
ここで書いたmybatis の mapper は、jsonb_set _lax を使わずに jsonb_set のままでも
null のセットを対応している。
PosetgreSQLのバージョン13 以上の限定になることはないであろう。

2024-01-13

PostgreSQL JSON演算子による JSON null の扱い

SQL

JSON型、JSONB型、の JSON の null の扱い
まずは JSON 演算子の基本から、
"b" 配列の１番目

SELECT '{"a":{"b":[{"c":1},{"c":2},{"c":null}]}}'::jsonb->'a'->'b'->1->>'c'

結果は文字型で

"b" 配列の２番目

SELECT '{"a":{"b":[{"c":1},{"c":2},{"c":null}]}}'::jsonb->'a'->'b'->2->>'c'

結果は文字型で

null

だから、COALESCE関数を使うと
"b" 配列の１番目は、

SELECT COALESCE('{"a":{"b":[{"c":1},{"c":2},{"c":null}]}}'::jsonb->'a'->'b'->1->>'c', 'This is null')

結果は文字型で

"b" 配列の２番目

SELECT COALESCE('{"a":{"b":[{"c":1},{"c":2},{"c":null}]}}'::jsonb->'a'->'b'->2->>'c', 'This is null')

結果は文字型で

This is null

JSONの空文字は、

SELECT '{"a":{"b":[{"c":1},{"c":2},{"c":""}]}}'::jsonb->'a'->'b'->2->>'c'

結果は空文字列

JSON空文字列とJSON null を同等に判定する。
"b" 配列の１番目は、

SELECT COALESCE(NULLIF('{"a":{"b":[{"c":1},{"c":2},{"c":""}]}}'::jsonb->'a'->'b'->1->>'c', '') ,'This is null')

結果は文字型で

"b" 配列の２番目は、

SELECT COALESCE(NULLIF('{"a":{"b":[{"c":1},{"c":2},{"c":""}]}}'::jsonb->'a'->'b'->2->>'c', '') ,'This is null')

も、

SELECT COALESCE(NULLIF('{"a":{"b":[{"c":1},{"c":2},{"c":null}]}}'::jsonb->'a'->'b'->2->>'c', '') ,'This is null')

も、結果は文字型で

This is null

つまり、
COALESCE( NULLIF( 対象カラム、''), 'This is null' )
で、
NULLIF関数が, NULLIF(value1, value2) の value1 とvalue2 が同じなら NULL を返し
異なれば、value1 の値を返す。ということを利用して、COALESCE 関数を次に実行するのである。

もっとヒステリックに余計な空白文字を除去するなら、
COALESCE( NULLIF( TRIM( 対象カラム )、''), 'This is null' )
であり

SELECT COALESCE(NULLIF(TRIM('{"a":{"b":[" ",""," c "]}}'::jsonb->'a'->'b'->>0), ''),'This is null

の結果は

This is null

SELECT COALESCE(NULLIF(TRIM('{"a":{"b":[" ",""," c "]}}'::jsonb->'a'->'b'->>2), ''),'This is null')

の結果は

である

2024-01-06

Json が同じかどうか判定する

Java Python

２つのJsonのキーと値が同じであるか判定する。
Java なら、
Jackson ライブラリの JsonNode に変換して、equals メソッドで判定する。
＝＝演算子ではダメ！

Python なら、
dict に変換して、＝＝演算子で判定して良い。

import json

dictA = json.loads('{ "a":12, "b":22 }')
dictB = json.loads('{ "b":22, "a":12 }')
if dictA==dictB:
     print("equal")

2024-01-02

JSON から PostgreSQL JSON演算子のパスを作る

Java

JSONの解析として、過去、いろいろと作ってきた。
Jackson でJSON読込み key-value の BiConsumer を処理する - Oboe吹きプログラマの黙示録
 Jackson でJSON読込み key-value の BiConsumer で、日付時刻も - Oboe吹きプログラマの黙示録
 Jackson JSON読込み、key-value 解析を柔軟にする - Oboe吹きプログラマの黙示録
 JSON の部分的な抽出 - Oboe吹きプログラマの黙示録
ここに入れてきたのだが、
github.com

PostgreSQL JSON 演算子によるパスの生成を作ることにした。
今まで作成した JsonNodeParse を継承して作成した。

package org.yipuran.json;

import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Stream;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.node.JsonNodeType;
/**
 * PostgreSQL JSON演算子パス生成用 JSON Parser
 * <pre>
 * JsonNodeParse の継承クラス
 * PostgreSQL JSON演算子パスの表現、
 *    'A'->'B'->>'C'  : C の値が、 文字列、数値、null
 *    'A'->'B'->'C'   : C の値が、JSonNode
 *    'A'->'D'->>2    : 配列D インデックス=2 の値
 *    'A'->'D'->2     : 配列D インデックス=2 のJSonNode
 * 上記の表現形式の Entry＜String, JsonNode＞ のStreamを生成する目的のクラス
 * </pre>
 */
public class JsonNodePgParse extends JsonNodeParse{

    /**
     * JsonNode Entry Stream生成.
     * <pre>
     * Entryのkeyは、PostgreSQL の JSON演算子 -> ->> とシングルクォート括りによるパス
     * value がJSONオブジェクトであれば、"->"
     * value が文字列、数値、null であれば、最後が、"->>"
     * </pre>
     * @param node JsonNode
     * @return Entry＜String, JsonNode＞ の Stream
     */
    @Override
    public Stream<Entry<String, JsonNode>> nodeStream(JsonNode node){
        Map<String, JsonNode> map = new HashMap<>();
        nodeJsonPg(node, "", map);
        return map.entrySet().stream();
    }

    private void nodeJsonPg(JsonNode node, String path, Map<String, JsonNode> map) {
        if (node.getNodeType().equals(JsonNodeType.OBJECT)) {
            for(Iterator<Entry<String, JsonNode>> it=node.fields(); it.hasNext();) {
                Entry<String, JsonNode> entry = it.next();
                String _path = path + "->" + "'" + entry.getKey() + "'";
                map.put(_path.substring(2), node);
                nodeJsonPg(entry.getValue(), path + "->" + "'"+ entry.getKey() + "'", map);
            }
        }else if(node.getNodeType().equals(JsonNodeType.ARRAY)){
            if (node.size() > 0){
                int x=0;
                for(Iterator<JsonNode> it=node.iterator(); it.hasNext();x++){
                    nodeJsonPg(it.next(), path + "->" + x, map);
                }
            }
        }else if(node.getNodeType().equals(JsonNodeType.NULL)){
            map.put(relaceLastallow(path.substring(2)), null);
        }else{
            map.put(relaceLastallow(path.substring(2)), node);
        }
    }
    private Pattern lastallow = Pattern.compile("\\->(?<=\\->)([^\\->]+$)");
    private String relaceLastallow(String key) {
        Matcher m = lastallow.matcher(key);
        if (m.find()) {
            return key.substring(0, m.start()) + m.group().replaceFirst("\\->", "->>");
        }
        return key;
    }
}

https://github.com/yipuran/yipuran-jack の version 1.4 としてこれを追加した

使用例）

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import org.yipuran.json.JsonNodePgParse;
import com.fasterxml.jackson.databind.JsonNode;

try{
    InputStream inst = Thread.currentThread().getContextClassLoader()
    .getResourceAsStream(JsonTest.class.getPackageName().replaceAll("\\.", "/")+"/"+"sample.json");
    String json = toStringFromInputStream(inst);
    JsonNodePgParse parse = new JsonNodePgParse();
    JsonNode node = parse.readTree(json);
    
    parse.nodeStream(node).sorted((a, b)->a.getKey().compareTo(b.getKey())).forEach(e->{
        System.out.println("key="+e.getKey() +"  value="+e.getValue());
    });
    
}catch(IOException e){
    e.printStackTrace();
}

2024-01-01

区切り、括りの正規表現

正規表現

区切り文字や括りの１行文字列で、最初 or 最後の一致を求める正規表現
例題）
対象文字列

'AsDate'->'C'->'2'->'June'

区切り文字「->」
最後の区切り文字以降

(?<=\->)([^\->]+$)

## match した文字列
'June'

最後の区切りを含んだ文字以降

\->(?<=\->)([^\->]+$)

## match した文字列
->'June'

１個目の区切り文字以降

(?<=\->)(.*)

## match した文字列
'C'->'2'->'June'

区切り文字による２番目（next matchあり）

(?<=\->)[^\->]*

## match した文字列
'C'

区切り文字による１番目（next matchあり）

(|<=\->)[^\->]*

## match した文字列
'AsDate'

シングルクォート
シングルクォートで括った最後の一致

'[^']*'+$

## match した文字列
'June'

シングルクォートで括った最初の一致

^'[^']*'

## match した文字列
'AsDate'