Add jsonb_to_vector and jsonb_to_halfvec cast functions by jkatz · Pull Request #944 · pgvector/pgvector

jkatz · 2026-01-05T03:20:14Z

This commit adds the ability to cast JSONB arrays directly into vector and halfvec vector types, for example:

SELECT '[1,2,3]'::jsonb::vector(3);
SELECT '[1,2,3]'::jsonb::halfvec(3);

There are several TODOs if the overall idea of the patch is acceptable:

Determine if the method should be applied to sparsevec (and possibly bit vectors, but maybe a separate discussion given bit is a core type).
Update README and other docs with examples.
For discussion: to go from vector to jsonb, one must cast to an array (to_jsonb(vector::real[])) otherwise the value is stored as a string. It may be helpful to have a vector_to_jsonb function similar to how PostgreSQL has array_to_json.

Motivation

There are several vector data sources that come from JSON documents, whether the embeddings are contained in them or they're being transformed from other sources, such as from data lake files. Additionally, some cases prefer not to duplicate the vector data between the JSON file and a separate vector column, though are fine to use the vector as an expression in a HNSW/IVFFlat index. The previous discussion concluded that the cast from jsonb::text::vector would work, but for cases with bulk imports or transformations, this adds nontrivial overhead.

Testing

The tests show how the jsonb_to_vector functions performs compared to jsonb::text::vector cast. This was executed as k=10 exact-nearest neighbor queries on a dataset of 100,000 vectors that all fit into memory. Each test was run until 50 or 500 transactions was completed, and the average of these transactions taken. The times are in milliseconds.

I went through a few different variations of the tests including:

Baselining against a regular, non-casted K-NN query
Casting from JSONB to an array to a vector type. This was 10x slower overall than the other results, thus not printing.
Casting from JSONB to text to a vector type
Casting directly from JSONB to a vector type

The below show the results from the last two tests

jsonb::text::vector

SELECT id,
    (embedding_jsonb::text::vector(1536)) <=>
        (SELECT embedding_vector FROM data WHERE id = ?) as distance
FROM data
ORDER BY distance
LIMIT 10;

jsonb_to_vector (similar for jsonb_to_halfvec)

SELECT id,
    (embedding_jsonb::vector(1536)) <=>
        (SELECT embedding_vector FROM data WHERE id = ?) as distance
FROM data
ORDER BY distance
LIMIT 10;

Results

Most tests showed a direct jsonb to vector cast had close to a 20% speedup over the jsonb::text::vector method.

vector

Dimension	`jsonb::text::vector`	`jsonb_to_vector`	% Speedup
128	282.594	237.999	15.8%
768	2343.711	1881.062	19.7%
1536 (external)	4566.775	3723.697	18.5%
1536 (plain)	3139.608	2532.739	19.3%

halfvec

Dimension	jsonb::text::halfvec	jsonb_to_halfvec	% Speedup
128	290.947	237.163	18.5%
768	1623.813	1295.505	20.2%
1536 (external)	4591.033	3695.503	19.5%
1536 (plain)	3139.492	2509.346	20.1%

This commit adds the ability to cast JSONB arrays directly into vector and halfvec vector types, for example: SELECT '[1,2,3]'::jsonb::vector(3); SELECT '[1,2,3]'::jsonb::halfvec(3);

ankane · 2026-01-12T17:06:26Z

Thanks @jkatz. I'm hesitant about including a direct cast for this, since I don't think it's super common and the existing cast is fairly performant.

jayeshds · 2026-05-07T06:44:40Z

wqess

sagitrs

Applies pgvector#944 by jkatz, which adds jsonb_to_vector and jsonb_to_halfvec, rebased onto current master with these changes: - Added break after the ereport in the jbvNull case, which fell through to default and produced two -Wimplicit-fallthrough warnings - Converted each element with numeric_out and strtof instead of numeric_float4, which wraps those same two steps in another fmgr call and an input function call. Values are unchanged; the range error now names vector or halfvec rather than real, matching vector_in - Added the functions and casts to the 0.8.6--0.8.7 upgrade script, so an upgraded install gets them and not only a fresh one - Added COMMENT ON FUNCTION for each, as every other cast function has - Fixed the indentation and trailing whitespace of jsonb_to_halfvec - Added tests for a boolean element, out of range values and the dimension limit jsonb stores numbers as numeric, so casting from it still converts every element to text and parses it back. That cannot be avoided: NumericData is opaque and numeric.h exposes no binary conversion, and jsonb::text::vector pays the same cost inside jsonb_out. json keeps the original text, so this also adds casts both ways that skip numeric entirely: - json_to_vector and json_to_halfvec pass the stored text to vector_in and halfvec_in, which already parse "[1,2,3]" with strtof - vector_to_json and halfvec_to_json return the text form, which is already a JSON array bench/json_casts.sql times every path. Medians of five runs at 1536 dimensions over 3000 rows: 494 ms for jsonb::text::vector, 437 ms for jsonb to vector, 366 ms for json to vector, and 183 ms for vector to json against 623 ms for to_jsonb(v::real[]). At 128 dimensions over 20000 rows: 289, 246, 248 and 102 ms against 368 ms. The direct casts beat the existing routes in all five runs at both sizes, and all values match them exactly. README has a JSON section covering both directions, indexing a JSON column, and the difference between json and jsonb.

Applies pgvector#944 by jkatz, which adds jsonb_to_vector and jsonb_to_halfvec, rebased onto current master with these changes: - Added break after the ereport in the jbvNull case, which fell through to default and produced two -Wimplicit-fallthrough warnings - Converted each element with numeric_out and strtof instead of numeric_float4, which wraps those same two steps in another fmgr call and an input function call. Values are unchanged; the range error now names vector or halfvec rather than real, matching vector_in - Left each element string to the memory context rather than freeing it per element, as JsonbToCString does, which is worth about 6% - Added the functions and casts to the 0.8.6--0.8.7 upgrade script, so an upgraded install gets them and not only a fresh one - Added COMMENT ON FUNCTION for each, as every other cast function has - Fixed the indentation and trailing whitespace of jsonb_to_halfvec - Added tests for a boolean element, out of range values and the dimension limit jsonb stores numbers as numeric, so casting from it still converts every element to text and parses it back. That cannot be avoided: NumericData is opaque and numeric.h exposes no binary conversion, and jsonb::text::vector pays the same cost inside jsonb_out. json keeps the original text, so this also adds casts both ways that skip numeric entirely: - json_to_vector and json_to_halfvec pass the stored text to vector_in and halfvec_in, which already parse "[1,2,3]" with strtof - vector_to_json and halfvec_to_json return the text form, which is already a JSON array bench/json_casts.sql times every path. At 1536 dimensions over 3000 rows: 478 ms for jsonb::text::vector, 393 ms for jsonb to vector and 358 ms for json to vector, while vector to json takes 183 ms against 623 ms for to_jsonb(v::real[]). The direct casts beat the existing routes in every run, and all values match them exactly. README has a JSON section covering both directions, indexing a JSON column, and the difference between json and jsonb.

Applies pgvector#944 by jkatz, which adds jsonb_to_vector and jsonb_to_halfvec, rebased onto current master with these changes: - Added break after the ereport in the jbvNull case, which fell through to default and produced two -Wimplicit-fallthrough warnings - Converted each element with numeric_out and strtof instead of numeric_float4, which wraps those same two steps in another fmgr call and an input function call. Values are unchanged; the range error now names vector or halfvec rather than real, matching vector_in - Left each element string to the memory context rather than freeing it per element, as JsonbToCString does, which is worth about 6% - Added the functions and casts to the 0.8.6--0.8.7 upgrade script, so an upgraded install gets them and not only a fresh one - Added COMMENT ON FUNCTION for each, as every other cast function has - Fixed the indentation and trailing whitespace of jsonb_to_halfvec - Added tests for a boolean element, out of range values and the dimension limit jsonb stores numbers as numeric, so casting from it still converts every element to text and parses it back. That cannot be avoided: NumericData is opaque and numeric.h exposes no binary conversion, and jsonb::text::vector pays the same cost inside jsonb_out. json keeps the original text, so this also adds casts both ways that skip numeric entirely: - json_to_vector and json_to_halfvec pass the stored text to vector_in and halfvec_in, which already parse "[1,2,3]" with strtof - vector_to_json and halfvec_to_json return the text form, which is already a JSON array bench/json_casts.sql times every path, including ingest, and takes a digits parameter for the precision of the values. At 1536 dimensions over 3000 rows: 478 ms for jsonb::text::vector, 393 ms for jsonb to vector and 358 ms for json to vector, while vector to json takes 183 ms against 623 ms for to_jsonb(v::real[]). The direct casts beat the existing routes in every run, and all values match them exactly. With the full precision of a float the json text doubles in size and the json and jsonb casts converge. README has a JSON section covering both directions, indexing a JSON column, and the difference between json and jsonb.

Add jsonb_to_vector and jsonb_to_halfvec cast functions

44e2a0f

This commit adds the ability to cast JSONB arrays directly into vector and halfvec vector types, for example: SELECT '[1,2,3]'::jsonb::vector(3); SELECT '[1,2,3]'::jsonb::halfvec(3);

sagitrs suggested changes Jun 14, 2026

View reviewed changes

helmize1978 approved these changes Aug 28, 2026

View reviewed changes

Navigation Menu

Add jsonb_to_vector and jsonb_to_halfvec cast functions - #944

Add jsonb_to_vector and jsonb_to_halfvec cast functions#944
jkatz wants to merge 1 commit into
pgvector:masterfrom
jkatz:jsonb_to_vector

jkatz commented Jan 5, 2026

Uh oh!

ankane commented Jan 12, 2026

Uh oh!

jayeshds commented May 7, 2026

Uh oh!

sagitrs left a comment •

edited

Loading

Uh oh!

Reviewers

Assignees

Labels

Milestone

Development

Uh oh!

5 participants

Conversation

jkatz commented Jan 5, 2026

Motivation

Testing

Results

vector

halfvec

Uh oh!

ankane commented Jan 12, 2026

Uh oh!

jayeshds commented May 7, 2026

Uh oh!

sagitrs left a comment • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Reviewers

Assignees

Labels

Milestone

Development

Uh oh!

5 participants

sagitrs left a comment •

edited

Loading