Extract structured data

The ai.extract function is a built-in AlloyDB AI tool that extracts specified structured details from unstructured text based on a provided schema. By embedding this capability directly into the database, AlloyDB for PostgreSQL lets you process unstructured data without building complex Extract, Transform, and Load (ETL) pipelines or external service integrations.

The data extraction function provides the following benefits:

Scalability: supports smart batching for AI functions —also referred to as AI function acceleration—which is an automatic query execution optimization feature in AlloyDB AI that transparently groups individual, row-by-row scalar AI function calls into batched model requests in the background during query execution. Also supports array-based and cursor-based processing to process thousands of rows efficiently.
Abstraction: provides a high-level abstraction that manages model invocation and data preparation automatically.

AlloyDB AI's data extraction function supports several use cases, including the following:

Healthcare: parse free-text medical notes into structured format fields like patient names, symptoms, and medications.
Real estate: transform unstructured property descriptions into specific data points like property type and bedroom count.
Legal technology: automatically identify and pull critical terms like governing law and liability caps from long-form legal documents.
Financial operations: process transcribed invoice text to accurately extract metadata and total amounts into a consistent format.

Before you begin

Make sure that you meet the following requirements before you use the ai.extract function:

PostgreSQL version: The ai.extract function is supported on PostgreSQL version 18 and later. Make sure that your AlloyDB cluster is running PostgreSQL 18 or later. For more information, see Supported database versions.
Extension version: Make sure that you have the latest version of the google_ml_integration extension (version 1.6.1 or higher) installed with Preview functionality enabled.

Enable the extension

Make sure that you have the latest version of the google_ml_integration extension (version 1.6.1 or higher) installed with Preview functionality enabled.

To enable the google_ml_integration.enable_ai_query_engine and google_ml_integration.enable_preview_ai_functions flags in AlloyDB, you use the SET command. These flags control access to the AI query engine and preview AI functions like ai.extract. The default value of these flags is off.

Make sure that your google_ml_integration extension is version 1.6.1 or higher. Check the version by running the following:
```
SELECT extversion FROM pg_extension WHERE extname = 'google_ml_integration';
```

To enable the flags for your current session, the entire database, or all databases on an instance, perform one or more of the following tasks:

To enable the flags for your current session, run the following:

SET google_ml_integration.enable_ai_query_engine = 'on';
SET google_ml_integration.enable_preview_ai_functions = 'on';

This change doesn't require a database restart.

To enable the flags persistently for a specific database, run the following:

ALTER DATABASE DATABASE_NAME SET google_ml_integration.enable_ai_query_engine = 'on';
ALTER DATABASE DATABASE_NAME SET google_ml_integration.enable_preview_ai_functions = 'on';

To enable the flags for all databases on an instance, set the google_ml_integration.enable_ai_query_engine and google_ml_integration.enable_preview_ai_functions database flags to on using the gcloud alloydb instances update command:

gcloud alloydb instances update INSTANCE_ID           --database-flags google_ml_integration.enable_ai_query_engine=on,google_ml_integration.enable_preview_ai_functions=on           --region=REGION_ID           --cluster=CLUSTER_ID           --project=PROJECT_ID

For more information, see Configure database flags.

Enable smart batching for AI functions

To achieve faster total query execution time when you run scalar ai.extract across multiple rows, enable smart batching for AI functions by setting the google_ml_integration.enable_ai_function_acceleration database flag to on:

SET google_ml_integration.enable_ai_function_acceleration = 'on';

When this flag is enabled, AlloyDB automatically groups individual scalar ai.extract calls into smart batches in the background.

Create an example table

To follow the extract function examples in this document, create a table and populate it with the following sample data.

CREATE TABLE IF NOT EXISTS sales_leads (
    lead_id INT PRIMARY KEY,
    raw_transcript TEXT,
    lead_status VARCHAR(20)
);

INSERT INTO sales_leads (lead_id, raw_transcript, lead_status) VALUES
(1, 'Spoke with Alex R., Director of IT at Example Organization. They are looking to upgrade their cloud storage.', 'new'),
(2, 'Call with Kai L., Senior DevOps Engineer at Example Organization. They are currently using MySQL and Redis. Interested in automating enterprise database backups, cross-region replication, and automated scaling.', 'new'),
(3, 'Meeting with Taylor K., VP of Engineering at Example Organization. They need a scalable managed PostgreSQL database for their analytics platform. Budget is approved for Q3 launch.', 'new');

Extract structured data from a single string

To extract structured details from a single text input, use the scalar version of ai.extract. For more information, see the ai.extract reference.

SELECT ai.extract(
  input => 'TEXT_CONTENT',
  output_schema => 'OUTPUT_SCHEMA',
  model_id => 'MODEL_ID' -- Optional. The default value is gemini-3.5-flash-lite.
);

Extract data from a table

The following example extracts structured information from the raw_transcript table column:

SELECT
  lead_id,
  ai.extract(
    input => raw_transcript,
    output_schema => '{"contact_name": {"type": "string"}, "company_name": {"type": "string"}, "job_title": {"type": "string"}}'
   ) AS extracted_info
FROM
  sales_leads
WHERE
  lead_status = 'new';

Extract fields based on provided JSON schema

The following example extracts specific fields from the input text based on the provided JSON schema:

SELECT ai.extract(
  input => 'Spoke with Alex R., Director of IT at Example Organization. They are looking to upgrade their cloud storage.',
  output_schema => '{"contact_name": {"type": "string"}, "job_title": {"type": "string"}, "company_name": {"type": "string"}, "need": {"type": "string"}}'
);

The following is the example output:

{
  "contact_name": "Alex R.",
  "job_title": "Director of IT",
  "company_name": "Example Organization",
  "need": "upgrade cloud storage"
}

Extract fields based on a natural language prompt

The following example extracts sales lead details using a descriptive text schema:

SELECT ai.extract(
  input => 'Meeting with Taylor K., VP of Engineering at Example Organization. They need a scalable managed PostgreSQL database for their analytics platform. Budget is approved for Q3 launch.',
  output_schema => 'Extract the following details as a clean, line-by-line list: Contact Name, Job Title, Company Name, Technical Requirement, Target Use Case, and Timeline.'
);

The following is the example output:

Contact Name: Taylor K.
Job Title: VP of Engineering
Company Name: Example Organization
Technical Requirement: Scalable managed PostgreSQL database
Target Use Case: Analytics platform
Timeline: Q3 launch

Extract fields with arrays

The following example extracts multiple values into array fields based on the output schema:

SELECT ai.extract(
  input => 'Call with Kai L., Senior DevOps Engineer at Example Organization. They are currently using MySQL and Redis. Interested in automating enterprise database backups, cross-region replication, and automated scaling.',
  output_schema => '{"contact_name": {"type": "string"}, "company_name": {"type": "string"}, "current_technologies": {"type": "array", "items": {"type": "string"}}, "interested_features": {"type": "array", "items": {"type": "string"}}}'
);

The following is the example output:

{
  "contact_name": "Kai L.",
  "company_name": "Example Organization",
  "current_technologies": ["MySQL", "Redis"],
  "interested_features": ["enterprise database backups", "cross-region replication", "automated scaling"]
}

Extract structured data in batches

To achieve faster total query execution time on larger datasets, use the array-based version of the function to process multiple inputs in a single call. For more information, see the ai.extract reference.

SELECT ai.extract(
  inputs => TEXT[],
  output_schema => TEXT,
  batch_size => INT, -- Optional. The default value is 10.
  model_id => VARCHAR(100) -- Optional. The default value is gemini-3.5-flash-lite.
);

The following example extracts structured details from multiple records:

SELECT
  ai.extract(
    inputs => array_agg(raw_transcript ORDER BY lead_id),
    output_schema => '{"contact_name": {"type": "string"}, "company_name": {"type": "string"}, "job_title": {"type": "string"}}',
    batch_size => 4
  )
FROM sales_leads;

Extract structured data using cursors

The cursor-based function is well suited for processing large datasets, because it lets the system stream data through the AI model in manageable batches without requiring all of the data to be loaded into memory at once. For more information, see the ai.extract reference.

The function signature for the cursor-based version of ai.extract is as follows:

CREATE OR REPLACE FUNCTION ai.extract(
    input_cursor REFCURSOR,
    output_schema TEXT,
    batch_size INT DEFAULT NULL,
    model_id VARCHAR(100) DEFAULT NULL)
  RETURNS REFCURSOR

The following example shows how to feed data to the ai.extract function row by row using a cursor:

DO $$
DECLARE
    input_cur REFCURSOR;
    output_cur REFCURSOR;
    result_record RECORD;
BEGIN
    -- Open the input cursor with the data to process
    OPEN input_cur FOR
        SELECT raw_transcript
        FROM sales_leads
        WHERE lead_status = 'new';

    -- Call ai.extract passing the input cursor
    output_cur := ai.extract(
        input_cursor => input_cur,
        output_schema => '{"contact_name": "string", "company_name": "string", "job_title": "string"}',
        batch_size => 10
    );

    -- Loop through the output cursor (columns: input TEXT, output TEXT)
    LOOP
        FETCH output_cur INTO result_record;
        EXIT WHEN NOT FOUND;

        RAISE NOTICE 'Extracted Data: %', result_record.output;
    END LOOP;

    CLOSE input_cur;
    CLOSE output_cur;
END $$;

What's next

Classify text.
Read the AI functions overview.

Extract structured data Stay organized with collections Save and categorize content based on your preferences.

Before you begin

Enable the extension

Enable smart batching for AI functions

Create an example table

Extract structured data from a single string

Extract data from a table

Extract fields based on provided JSON schema

Extract fields based on a natural language prompt

Extract fields with arrays

Extract structured data in batches

Extract structured data using cursors

What's next

Extract structured data