MySQL のJSON型の path書式

PostgreSQL とは違って、ルートを$文字で指定して、"->" による連結ではなく
JSON列名 -> path
 あるいは、
JSON列名 ->> path
で、文字列とての参照になる。
path 部分が、$から始まってJSONキーを '.' ピリオド区切り文字で指定する。

例) jdataという列データが以下の時、

{
  "limit": "2024-03-01",
  "other": {
    "len": 38,
    "label": "Abc",
    "width": 23,
    "options": [
      "A",
      "B",
      "C"
    ]
  },
  "price": 320
}

other の label を抽出

SELECT jdata->>'$.other.label' 

priceを文字列として抽出

SELECT jdata->>'$.price'     

priceを抽出 → DECIMAL に CASTする

SELECT cast(jdata->'$.price' as DECIMAL) 

limit の日付を DATE 型へ

SELECT cast(jdata->'$.limit' as DATE)  

配列の先頭

SELECT jdata->>'$.other.options[0]'    

MySQL の JSON_SET関数

PostgreSQL JSON_SET/JSONB_SET と同じ要領で使うと失敗する。
  JSON_SET(json_doc, path, val[, path, val] ...)

  • path の指定は、JSONルートの $ から、区切り文字 '.' ピリオドで指定する。
  • PostgreSQL のようにキーが存在しなかったらセットする/しないの
    true/false 指定はなく存在しない場合は挿入、存在すれば更新である。

次のようにPostgreSQL と同様に path を指定してしまうと、

SET @j = '{"title": "Alpha", "other": {"label": "Abc", "width": 23} }';
SELECT json_set(@j, 'other,width', 80)

Error Code: 3143. Invalid JSON path expression
となる。
正しくは、、

SET @j = '{"title": "Alpha", "other": {"label": "Abc", "width": 23} }';
SELECT json_set(@j, '$.other.width', 80)

配列の更新は、[ index ] で、

SET @j = '{"title": "Alpha", "other": {"label": "Abc", "width": 90, "options":[ "X", "B", "C"  ]} }';
SELECT json_set(@j, '$.other.options[1]', 'a')
{"other": {"label": "Abc", "width": 90, "options": ["X", "a", "C"]}, "title": "Alpha"}

null をセットする時、PostgreSQL のケースとは異なって、、
json_set/jsonb_set で NULL をセットする時の注意 - Oboe吹きプログラマの黙示録
直接、null を指定して実行する。

SET @j = '{"title": "Alpha", "other": {"label": "Abc", "width": 23} }';
SELECT json_set(@j, '$.other.width', null)
{"other": {"label": "Abc", "width": null}, "title": "Alpha"}

JSON の一部の key value を削除する ( Jackson JsonNode )

巨大なJSON、階層が深かったり値の文字列がとても長かったりする時は、整形したとしても
とても見にくく目視確認に困る。
確認しなくても済むものは、取り除いてしまいたい。
以下、そんなに大きくないJSON でそういう場合の方法、Jacksonライブラリの JsonNode
対象JSON が読み込めている前提でサンプルを示す。

{
  "A" : 123,
  "B" : {
    "B01" : "b01-01234",
    "B02" : {
      "detail" : "詳細~",
      "name" : "orange"
    }
  },
  "C" : [ {
    "C01" : {
      "size" : 230
    }
  }, {
    "C02" : {
      "size" : 380
    }
  }, {
    "C03" : {
      "size" : 670
    }
  } ],
  "D" : "Document"
}

このJSON文字列が読み込んだ結果の JsonNode インスタンスnode だとする。

"D" : "Document" を取り除く場合
ObjectNode に cast して、JsonNode remove(String propertyName) メソッドを実行する。

((ObjectNode)node).remove("D");

node インスタンスから "D" key と valueが削除されてしまうことに注意

深い階層は、JsonNode の path(String) で対象ノードまで連結して JsonNode を求めてから
ObjectNode に cast して remove(String) を実行する。

上の、"B" ⇒ "B02" ⇒ "detail" を削除する場合、

((ObjectNode)(node.path("B").path("B02"))).remove("detail");

配列の1個を削除する場合は、JsonNode から ArrayNode に cast して
remove(int index) メソッドで削除する

((ArrayNode)(node.path("C"))).remove(1);

これら、

((ObjectNode)node).remove("D");
((ObjectNode)(node.path("B").path("B02"))).remove("detail");
((ArrayNode)(node.path("C"))).remove(1);

を実行した結果の JsonNode node は、以下になる。

{
  "A" : 123,
  "B" : {
    "B01" : "b01-01234",
    "B02" : {
      "name" : "orange"
    }
  },
  "C" : [ {
    "C01" : {
      "size" : 230
    }
  }, {
    "C03" : {
      "size" : 670
    }
  } ]
}

PostgreSQL mybatis JSONB型があるテーブルへの insert

例)jbooks というテーブルの列名=jdata が JSONB型だとする。

ObjectMapper の writeValueAsString に頼る方法
SQLMap XML で bindタグで、writeValueAsString が働くようにする。

<insert id="insertJbook">
	<bind name="jdata" value="objectMapper.writeValueAsString(object)" />
INSERT INTO jbooks (id, title, jdata)
VALUES(#{id}, #{title}, '${jdata}'::jsonb )
</insert>

'${jdata}'::jsonb のように文字列出力してマッピングする。
Mapper Interface はSQLMap XMLに合わせて以下のとおり、
第3引数に、ObhjectMapperインスタンス、第4引数にJSONとして格納するインスタンスを指定

public int insertJbook(@Param("id")int id,
                       @Param("title")String title,
                       @Param("objectMapper")ObjectMapper objectMapper,
                       @Param("object")Object object
                       );

JSON用のTypeHandleを用意してタイプハンドラを使用する方法
用意するタイプハンドラは、
こちらのように用意する。MySQL 使用する時に書いたものだが、PostgreSQL でも注意すれば使える。
(⇒注意すること。 ::jsonb によるJSON 文字列から JSONB型への変換を指定すれば良い)
MySQL JSON型をmybatis で読み書きするサンプル - Oboe吹きプログラマの黙示録
Mapper Interface は、JSONB型の列で用意したタイプハンドラを指定

@Insert("INSERT INTO jbooks (id, title, jdata)VALUES(#{id}, #{title}, #{jdata, typeHandler=org.sample.JsonTypeHandler}::jsonb )")
public int insertJbook(@Param("id")int id, @Param("title")String title, @Param("jdata")ItemDto object);

用意したタイプハンドラ

package org.sample;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.format.DateTimeFormatter;
import org.yipuran.mybatis.types.AbstractJsonTypeHandler;
import com.fasterxml.jackson.databind.module.SimpleModule;
import com.fasterxml.jackson.datatype.jsr310.JavaTimeModule;
import com.fasterxml.jackson.datatype.jsr310.deser.LocalDateDeserializer;
import com.fasterxml.jackson.datatype.jsr310.deser.LocalDateTimeDeserializer;
import com.fasterxml.jackson.datatype.jsr310.deser.LocalTimeDeserializer;
import com.fasterxml.jackson.datatype.jsr310.ser.LocalDateSerializer;
import com.fasterxml.jackson.datatype.jsr310.ser.LocalDateTimeSerializer;
import com.fasterxml.jackson.datatype.jsr310.ser.LocalTimeSerializer;

public class JsonTypeHandler<T> extends AbstractJsonTypeHandler<T> {
   public JsonTypeHandler(Class<T> clazz){
      super(clazz);
   }
   @Override
   public SimpleModule getModule(){
      JavaTimeModule jtm = new JavaTimeModule();
      jtm.addSerializer(new LocalDateTimeSerializer(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS")))
     .addSerializer(new LocalDateSerializer(DateTimeFormatter.ofPattern("yyyy-MM-dd")))
      .addSerializer(LocalTimeSerializer.INSTANCE)
      .addDeserializer(LocalDateTime.class, new LocalDateTimeDeserializer(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS")))
     .addDeserializer(LocalDate.class, new LocalDateDeserializer(DateTimeFormatter.ofPattern("yyyy-MM-dd")))
      .addDeserializer(LocalTime.class, new LocalTimeDeserializer(DateTimeFormatter.ISO_LOCAL_TIME));
      return jtm;
   }
}

import して継承している AbstractJsonTypeHandler は、
以下に書いている。

https://github.com/yipuran/yipuran-mybatis/blob/master/src/main/java/org/yipuran/mybatis/types/AbstractJsonTypeHandler.java

LocalDateTime など、細かい仕様を1つで引き受けて定義するなら、
カスタマイズした TypeHandller を用意した方が楽であろう。

PostgreSQL JSONB 抽出時に出力したくないキー、値を指定する

例)
列名 jdata というJSONB列に以下の json データが入っているとする。

{
    "fr": 11,
    "key": "E",
    "pub": "ara",
    "item": {
        "name": "orange",
        "group": {
            "x01": "AX193",
            "x02": "AX248",
            "c923": "A_982"
        },
        "price": 231,
        "oprion": " "
    },
    "point": 14
}

直下のキー "point" をJSONから取り除く。
- 演算子で削除できるのは、直下のkeyを指定する場合のみ

SELECT JSONB_PRETTY(jdata - 'point') as data2,

'point' key だけが削除される。

階層の深いところ、
jdata->'item'->'group'->'c923' を削除したい時は - 演算子ではなく、
#- 演算子でパスを { } で括ったカンマ区切りで指定する

SELECT JSONB_PRETTY(jdata #- '{item,group,c923}') as data3

data3 の結果は、

{
    "fr": 11,
    "key": "E",
    "pub": "ara",
    "item": {
        "name": "orange",
        "group": {
            "x01": "AX193",
            "x02": "AX248"
        },
        "price": 231,
        "oprion": " "
    },
    "point": 14
}

元のjsonbデータの jdata->'item' から削除する場合は、

SELECT jsonb_pretty(jdata->'item' #- '{group,c923}') as data4

data4 の結果は、

{
    "name": "orange",
    "group": {
        "x01": "AX193",
        "x02": "AX248"
    },
    "price": 231,
    "oprion": " "
}

コンテナにファイルをコピー/ダウンロード

OpenShift のコマンドのメモ

oc rsync <source> <destination> [-c <container>]

/home/user/source をコンテナ内 /src にコピー

$ oc rsync /home/user/source devpod1234:/src

これの逆

$ oc rsync devpod1234:/src /home/user/source

2つのログファイルをマージする

2つのログファイルをマージするツールを Python で作りました。
【作った理由】
Windows にダウンロードしたりしたログファイルを簡単にドラッグアンドドロップの操作で
ログ出力のログのタイムラインに沿ってマージしたい。
→ パラメータで2個のログファイルパスと出力先を指定なんて面倒な操作をしたくない
・PyInstaller で作れば Python をインストールしていないPCでも実行できるツールを提供できる。
・手作業でログファイルを開いてコピペ作業でマージするなんて、ゾッとするおぞましさ。。。

【ツールの仕様】
・ログファイル中身の各行の先頭は、以下の日付時刻書式のいずれかで書かれている。
yyyy-MM-dd HH:mm:ss.SSS
yyyy-MM-dd HH:mm:ss.SSSSSS
yyyy-MM-dd HH:mm:ss
yyyy/MM/dd HH:mm:ss.SSS
yyyy/MM/dd HH:mm:ss.SSSSSS
yyyy/MM/dd HH:mm:ss

年月日が、'/' 区切りか、'-' 区切り どちらでも良い、秒以下は、ミリ秒かマイクロ秒の精度か、秒以下無し。
・1つのログの中に日付書式以降に改行があれば、その改行された行はその時刻のログとしてマージされる。
・マージされて出力するフォルダを指定して、出力される結果のファイル名は、
 マージ対象のファイル名(拡張子を除く)を '_' アンダーバー文字で連結したファイル名で、拡張子は .log とする
tkinterは使用しない。tcl/tk は使わない。

ツールの起動の __main__ 部分

if __name__ == '__main__':
    args = sys.argv
    if len(args)==2 or len(args)==3:
        if len(args)==2:
            if os.path.isfile(args[1]):
                print(f'1個目マージ対象ログファイル :{args[1]}')
                log2nd = input('2個目マージ対象ログファイル ->')
                if os.path.isfile(log2nd):
                    outdir = input('マージ出力先フォルダ ->')
                    if os.path.isdir(outdir):
                        logmerge = Logmerge()
                        logmerge.exec(args[1], log2nd, outdir)
                    else:
                        print(f'out directory Error : {outdir}')
                else:
                    print(f'Not Found logfile : {log2nd}')
            else:
                print(f'Not Found logfile : {args[1]}')
        else:
            if os.path.isfile(args[1]):
                print(f'1個目マージ対象ログファイル :{args[1]}')
                if os.path.isfile(args[2]):
                    print(f'2個目マージ対象ログファイル :{args[2]}')
                    outdir = input('マージ出力先フォルダ ->')
                    if os.path.isdir(outdir):
                        logmerge = Logmerge()
                        logmerge.exec(args[1], args[2], outdir)
                    else:
                        print(f'out directory Error : {outdir}')
                else:
                    print(f'Not Found logfile : {args[2]}')
            else:
                print(f'Not Found logfile : {args[1]}')
            pass
    else:
        print('Usage: logmerge.exe logfile1 logfile2')
    os.system("PAUSE")

PyInstaller で EXE化したファイルアイコンに、目的のログファイルをドラッグアンドドロップすれば、
sys.argv にそのパスが入ってくることを利用している。
さらに、起動後、input() 関数で、2個目のログファイルのドラッグ、そして
出力先フォルダのドラッグも入力される。
input() 関数で、ドラッグ入力されたら、改行キー(リターンキー)叩けば良いだけだ

class Logmerge の内容

import os
import sys
import datetime
import re

class Logmerge():
    def __init__(self):
        self.timeptns = [
(r'^\d{4}\-(0[1-9]|1[012])\-(0[1-9]|[12][0-9]|3[01]) (0[0-9]|1[0-9]|2[0-3]):(0[0-9]|[0-5][0-9]):(0[0-9]|[0-5][0-9])\.[0-9]{3,6}$', '%Y-%m-%d %H:%M:%S.%f'),
(r'^\d{4}\-(0[1-9]|1[012])\-(0[1-9]|[12][0-9]|3[01]) (0[0-9]|1[0-9]|2[0-3]):(0[0-9]|[0-5][0-9]):(0[0-9]|[0-5][0-9])$', '%Y-%m-%d %H:%M:%S'),
(r'^\d{4}/(0[1-9]|1[012])/(0[1-9]|[12][0-9]|3[01]) (0[0-9]|1[0-9]|2[0-3]):(0[0-9]|[0-5][0-9]):(0[0-9]|[0-5][0-9])\.[0-9]{3,6}$', '%Y/%m/%d %H:%M:%S.%f'),
(r'^\d{4}/(0[1-9]|1[012])/(0[1-9]|[12][0-9]|3[01]) (0[0-9]|1[0-9]|2[0-3]):(0[0-9]|[0-5][0-9]):(0[0-9]|[0-5][0-9])$', '%Y/%m/%d %H:%M:%S'),
        ]
    def exec(self, fileA, fileB, outdir)->None:
        with open(fileA, mode='r', newline='', encoding='utf-8') as fa:
            with open(fileB, mode='r', newline='', encoding='utf-8') as fb:
                outname = os.path.splitext(os.path.basename(fileA))[0] + "_" + os.path.splitext(os.path.basename(fileB))[0]
                outpath = "%s\%s.log" % (outdir, outname)
                with open(outpath, mode='w', encoding='utf-8') as out:
                    a = fa.readline()
                    b = fb.readline()
                    while len(a) > 0 or len(b) > 0:
                        dta = self.logtime(a)
                        dtb = self.logtime(b)
                        ca = dta != None
                        cb = dtb != None
                        if dta != None and dtb != None:
                            if dta < dtb:
                                out.write(a.rstrip()+'\n')
                                while True:
                                    a = fa.readline()
                                    if len(a)==0: break
                                    dta = self.logtime(a)
                                    if dta==None:
                                        out.write(a.rstrip()+'\n')
                                    else:
                                        break
                            else:
                                out.write(b.rstrip()+'\n')
                                while True:
                                    b = fb.readline()
                                    if len(b)==0: break
                                    dtb = self.logtime(b)
                                    if dtb==None:
                                        out.write(b.rstrip()+'\n')
                                    else:
                                        break
                        else:
                            if len(a) > 0:
                                out.write(a.rstrip()+'\n')
                                while True:
                                    a = fa.readline()
                                    if len(a) == 0: break
                                    dta = self.logtime(a)
                                    if dta == None:
                                        out.write(a.rstrip()+'\n')
                                    else:
                                        break
                            if len(b) > 0:
                                out.write(b)
                                while True:
                                    b = fb.readline()
                                    if len(b) == 0: break
                                    dtb = self.logtime(b)
                                    if dtb == None:
                                        out.write(b.rstrip()+'\n')
                                    else:
                                        break
                    print(f'マージ完了\n{outpath}')
        pass
    def logtime(self, data:str)->datetime.datetime:
        matches = [t for t in self.timeptns if re.fullmatch(t[0], data[:26])]
        if len(matches) == 1:
            return datetime.datetime.strptime(data[:26], matches[0][1])
        else:
            matches = [t for t in self.timeptns if re.fullmatch(t[0], data[:23])]
            if len(matches) == 1:
                return datetime.datetime.strptime(data[:23], matches[0][1])
        return None

実行例
ログファイル1個目をドラッグする

起動されて、

改行してから、2個目もドラッグする

2個入れたら、改行する

出力先フォルダをドラッグで入力して改行する

マージ完了する。
はじめに2個ログファイルを選択してドラッグすれば、出力先のフォルダのドラッグ入力だけで済む。