LangChain PGVector メモ

# PGVector

https://python.langchain.com/v0.1/docs/integrations/vectorstores/pgvector/

# langchain_pg_collection

collection レコードが必要なのでいれる

```sql
insert into langchain_pg_collection(name, uuid) values ('collection', 'bade70a6-de45-40b9-b98b-65490df7c88b');
```

# langchain_pg_embedding

ランダムなuuidを生成するためのextensionを入れる

```sql
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
```

## 例. テストデータを入れる

```sql
INSERT INTO langchain_pg_embedding (collection_id, document, cmetadata, uuid) SELECT 'bade70a6-de45-40b9-b98b-65490df7c88b'::uuid, 'YOUR_DOCUMENT_VALUE','{"data_source_type": "slack", "id": "YOUR_SLACK_ID_VALUE"}'::jsonb, uuid_generate_v4() FROM generate_series(1, 2);
```

<details><summary>もっと複雑なデータ</summary>

例1. metadata `slack` をいれたレコード10件をinsert


```sql
INSERT INTO langchain_pg_embedding (collection_id, document, cmetadata, uuid)
SELECT
    'bade70a6-de45-40b9-b98b-65490df7c88b'::uuid, -- 指定された collection_id
    'YOUR_DOCUMENT_VALUE', -- ドキュメント値
    '{"data_source_type": "slack", "id": "YOUR_SLACK_ID_VALUE"}'::jsonb, -- cmetadata値
    uuid_generate_v4() -- 新しいUUIDを生成
FROM
    generate_series(1, 10); -- 10回繰り返す
```

例2. metadata `slack`と `confluence`をいれたレコードを10件ずつinsert

```sql
INSERT INTO langchain_pg_embedding (collection_id, document, cmetadata, uuid)
SELECT
    'bade70a6-de45-40b9-b98b-65490df7c88b'::uuid, -- 指定された collection_id
    'YOUR_DOCUMENT_VALUE', -- ドキュメント値
    '{"data_source_type": "confluence", "id": "123456789"}'::jsonb, -- cmetadata値
    uuid_generate_v4() -- 新しいUUIDを生成
FROM
    generate_series(1, 10) -- 10回繰り返す
UNION ALL
SELECT
    'bade70a6-de45-40b9-b98b-65490df7c88b'::uuid, -- 指定された collection_id
    'YOUR_DOCUMENT_VALUE', -- ドキュメント値
    '{"data_source_type": "slack", "id": "YOUR_SLACK_ID_VALUE"}'::jsonb, -- cmetadata値
    uuid_generate_v4() -- 新しいUUIDを生成
FROM
    generate_series(1, 10); -- 10回繰り返す
```

</details>

## JSONBフィールドの値のWhere

```sql
select * from langchain_pg_embedding where cmetadata->'monitor'->>'monitor_id' = '12345' ;
```

https://zenn.dev/snagasawa/articles/postgresql_json_and_jsonb_type

## 例. cmetadataを部分的にだけ更新する

```sql
update langchain_pg_embedding set cmetadata=jsonb_set(cmetadata, '{name}', '"Mary"', true) where uuid='22859334-b373-462b-adb2-c6d7262f72a8';
```

```sql
select * from langchain_pg_embedding ;
            collection_id             | embedding |      document       |                               cmetadata                               | custom_id |                 uuid                 
--------------------------------------+-----------+---------------------+-----------------------------------------------------------------------+-----------+--------------------------------------
 bade70a6-de45-40b9-b98b-65490df7c88b |           | YOUR_DOCUMENT_VALUE | {"id": "123456789", "data_source_type": "confluence"}                 |           | 98702d8e-ec61-4665-9f99-ff4c21c2d09c
 bade70a6-de45-40b9-b98b-65490df7c88b |           | YOUR_DOCUMENT_VALUE | {"id": "123456789", "data_source_type": "confluence"}                 |           | efa194d7-317f-480c-ace4-f7cf1c8aaf91
 bade70a6-de45-40b9-b98b-65490df7c88b |           | YOUR_DOCUMENT_VALUE | {"id": "123456789", "data_source_type": "confluence"}                 |           | 401793b1-937d-42de-a703-b141a223918f
 bade70a6-de45-40b9-b98b-65490df7c88b |           | YOUR_DOCUMENT_VALUE | {"id": "123456789", "data_source_type": "confluence"}                 |           | 031245b2-02cc-4a1c-b294-81d34a0aa3f1
 bade70a6-de45-40b9-b98b-65490df7c88b |           | YOUR_DOCUMENT_VALUE | {"id": "123456789", "data_source_type": "confluence"}                 |           | ef2b39ef-c9c9-45aa-94a5-390e71cd581d
 bade70a6-de45-40b9-b98b-65490df7c88b |           | YOUR_DOCUMENT_VALUE | {"id": "123456789", "data_source_type": "confluence"}                 |           | a3428ac6-f2ef-4bfb-8296-fd03c5f85dc0
 bade70a6-de45-40b9-b98b-65490df7c88b |           | YOUR_DOCUMENT_VALUE | {"id": "123456789", "data_source_type": "confluence"}                 |           | b9cd2fef-6f9b-491e-85b3-210f42ac0707
 bade70a6-de45-40b9-b98b-65490df7c88b |           | YOUR_DOCUMENT_VALUE | {"id": "123456789", "data_source_type": "confluence"}                 |           | f28dcc8d-8084-4949-ae47-91bb8312bc45
 bade70a6-de45-40b9-b98b-65490df7c88b |           | YOUR_DOCUMENT_VALUE | {"id": "123456789", "data_source_type": "confluence"}                 |           | 8f30d7e7-1b80-4d8a-8c44-c91491db5a88
 bade70a6-de45-40b9-b98b-65490df7c88b |           | YOUR_DOCUMENT_VALUE | {"id": "YOUR_SLACK_ID_VALUE", "data_source_type": "slack"}            |           | 60a63b1f-5a99-4446-8e20-35899305ca88
 bade70a6-de45-40b9-b98b-65490df7c88b |           | YOUR_DOCUMENT_VALUE | {"id": "YOUR_SLACK_ID_VALUE", "data_source_type": "slack"}            |           | 70b6ea5c-1135-4e78-a36b-7657d85fb0e1
 bade70a6-de45-40b9-b98b-65490df7c88b |           | YOUR_DOCUMENT_VALUE | {"id": "YOUR_SLACK_ID_VALUE", "data_source_type": "slack"}            |           | 55fb848d-1aa8-426b-96a5-26b003eb273c
 bade70a6-de45-40b9-b98b-65490df7c88b |           | YOUR_DOCUMENT_VALUE | {"id": "YOUR_SLACK_ID_VALUE", "data_source_type": "slack"}            |           | a50e46ac-4e4e-455d-b21e-b64200f3cf29
 bade70a6-de45-40b9-b98b-65490df7c88b |           | YOUR_DOCUMENT_VALUE | {"id": "YOUR_SLACK_ID_VALUE", "data_source_type": "slack"}            |           | 18813750-20bf-432b-9220-29695815e0e1
 bade70a6-de45-40b9-b98b-65490df7c88b |           | YOUR_DOCUMENT_VALUE | {"id": "YOUR_SLACK_ID_VALUE", "data_source_type": "slack"}            |           | 19328772-ddf9-46fe-98a2-12ebd0f137ab
 bade70a6-de45-40b9-b98b-65490df7c88b |           | YOUR_DOCUMENT_VALUE | {"id": "YOUR_SLACK_ID_VALUE", "data_source_type": "slack"}            |           | 44669f77-f6a1-4d61-b3ee-355b4b7cb3c6
 bade70a6-de45-40b9-b98b-65490df7c88b |           | YOUR_DOCUMENT_VALUE | {"id": "YOUR_SLACK_ID_VALUE", "data_source_type": "slack"}            |           | b313d7a5-16ed-4ec5-bc84-c8c8389e49d4
 bade70a6-de45-40b9-b98b-65490df7c88b |           | YOUR_DOCUMENT_VALUE | {"id": "YOUR_SLACK_ID_VALUE", "data_source_type": "slack"}            |           | ecbba79b-e5f1-44ca-8939-3a718ee8af03
 bade70a6-de45-40b9-b98b-65490df7c88b |           | YOUR_DOCUMENT_VALUE | {"id": "YOUR_SLACK_ID_VALUE", "data_source_type": "slack"}            |           | 59689e99-23c9-4360-8d29-4f7d2de0eab9
 bade70a6-de45-40b9-b98b-65490df7c88b |           | YOUR_DOCUMENT_VALUE | {"id": "123456789", "name": "Mary", "data_source_type": "confluence"} |           | 22859334-b373-462b-adb2-c6d7262f72a8
(20 rows)
```

ref: https://stackoverflow.com/questions/34022484/postgresql-jsonb-update-key-with-a-where-clause

## 例. jsonbのfieldをbooleanとしてセットしてつかう

```sql
update langchain_pg_embedding set cmetadata=jsonb_set(cmetadata, '{has_human_reply}', 'true', true) where cmetadata->>'id' = '123456789';
```

```sql
select * from langchain_pg_embedding where (cmetadata->>'has_human_reply')::boolean;
            collection_id             | embedding |      document       |                                           cmetadata                                            | custom_id |                 uuid                 
--------------------------------------+-----------+---------------------+------------------------------------------------------------------------------------------------+-----------+--------------------------------------
 bade70a6-de45-40b9-b98b-65490df7c88b |           | YOUR_DOCUMENT_VALUE | {"id": "123456789", "name": "Mary", "has_human_reply": true, "data_source_type": "confluence"} |           | 98702d8e-ec61-4665-9f99-ff4c21c2d09c
 bade70a6-de45-40b9-b98b-65490df7c88b |           | YOUR_DOCUMENT_VALUE | {"id": "123456789", "name": "Mary", "has_human_reply": true, "data_source_type": "confluence"} |           | efa194d7-317f-480c-ace4-f7cf1c8aaf91
 bade70a6-de45-40b9-b98b-65490df7c88b |           | YOUR_DOCUMENT_VALUE | {"id": "123456789", "name": "Mary", "has_human_reply": true, "data_source_type": "confluence"} |           | 401793b1-937d-42de-a703-b141a223918f
 bade70a6-de45-40b9-b98b-65490df7c88b |           | YOUR_DOCUMENT_VALUE | {"id": "123456789", "name": "Mary", "has_human_reply": true, "data_source_type": "confluence"} |           | 031245b2-02cc-4a1c-b294-81d34a0aa3f1
 bade70a6-de45-40b9-b98b-65490df7c88b |           | YOUR_DOCUMENT_VALUE | {"id": "123456789", "name": "Mary", "has_human_reply": true, "data_source_type": "confluence"} |           | ef2b39ef-c9c9-45aa-94a5-390e71cd581d
 bade70a6-de45-40b9-b98b-65490df7c88b |           | YOUR_DOCUMENT_VALUE | {"id": "123456789", "name": "Mary", "has_human_reply": true, "data_source_type": "confluence"} |           | a3428ac6-f2ef-4bfb-8296-fd03c5f85dc0
 bade70a6-de45-40b9-b98b-65490df7c88b |           | YOUR_DOCUMENT_VALUE | {"id": "123456789", "name": "Mary", "has_human_reply": true, "data_source_type": "confluence"} |           | b9cd2fef-6f9b-491e-85b3-210f42ac0707
 bade70a6-de45-40b9-b98b-65490df7c88b |           | YOUR_DOCUMENT_VALUE | {"id": "123456789", "name": "Mary", "has_human_reply": true, "data_source_type": "confluence"} |           | f28dcc8d-8084-4949-ae47-91bb8312bc45
 bade70a6-de45-40b9-b98b-65490df7c88b |           | YOUR_DOCUMENT_VALUE | {"id": "123456789", "name": "Mary", "has_human_reply": true, "data_source_type": "confluence"} |           | 8f30d7e7-1b80-4d8a-8c44-c91491db5a88
 bade70a6-de45-40b9-b98b-65490df7c88b |           | YOUR_DOCUMENT_VALUE | {"id": "123456789", "name": "Mary", "has_human_reply": true, "data_source_type": "confluence"} |           | 22859334-b373-462b-adb2-c6d7262f72a8
```

## sqlalchemyで書くと


```python
        Session = sessionmaker(bind=self.engine)
        with Session() as session:
            stmt = (
                update(CustomEmbeddingStore)
                .where(CustomEmbeddingStore.cmetadata.op("->>")("source") == original_slack_url)
                # .values(cmetadata={"has_human_reply": True}) # worked but cleared all other attributes
                .values(cmetadata=func.jsonb_set(CustomEmbeddingStore.cmetadata, ["has_human_reply"], cast("true", JSONB)))
            )
            result = session.execute(stmt)
            embeddings_rows = result.rowcount
```