Postgres 17 has JSON_TABLE, which looks pretty cool.

https://medium.com/@atarax/finally-json-table-is-here-postgres-17-a9b5245649bd

Oct 06 '24 13:10 tacman

Wonderful! Can you please help the project and suggest and open a PR for it? I will review and help push it over the line for the next release. ❤️

Oct 06 '24 13:10 martin-georgiev

That may be beyond my skill set, but when I start using JSON_TABLE in my NativeQueries, I'll look into how to add it. I suspect it will be a bit more complicated than the other JSON functions you provide.

Thanks again for this library.

Oct 06 '24 13:10 tacman

@coderabbitai can you propose changes of what such an implementation may look like?

Apr 06 '25 10:04 martin-georgiev

[!TIP] For best results, initiate chat on the files or code changes.

🧩 Analysis chain

🏁 Script executed:

#!/bin/bash
# Let's first understand the repository structure
ls -la

Length of output: 1000

🏁 Script executed:

#!/bin/bash
# Check the README to understand the project
cat README.md

Length of output: 3998

🏁 Script executed:

#!/bin/bash
# Let's check the issue #234 to understand what is being requested
gh issue view 234

Length of output: 294

🏁 Script executed:

#!/bin/bash
# Let's look at the source code structure
find src -type f | sort

Length of output: 10646

🏁 Script executed:

#!/bin/bash
# Let's examine some of the existing JSON functions to understand the pattern
cat src/MartinGeorgiev/Doctrine/ORM/Query/AST/Functions/BaseFunction.php

Length of output: 3129

🏁 Script executed:

#!/bin/bash
# Let's look at one of the JSON functions to understand the implementation pattern
cat src/MartinGeorgiev/Doctrine/ORM/Query/AST/Functions/JsonGetField.php

Length of output: 655

@martin-georgiev Based on my analysis of the repository structure and the JSON_TABLE function in PostgreSQL 17, here's a proposed implementation:

First, I'll create a new file: src/MartinGeorgiev/Doctrine/ORM/Query/AST/Functions/JsonTable.php


declare(strict_types=1);

namespace MartinGeorgiev\Doctrine\ORM\Query\AST\Functions;

/**
 * Implementation of PostgreSQL's JSON_TABLE function.
 * 
 * JSON_TABLE transforms JSON data into a relational format (virtual table).
 * This is available in PostgreSQL 17+.
 *
 * @see https://www.postgresql.org/docs/17/functions-json.html
 * @since [Next Version]
 *
 * @author [Your Name]
 */
class JsonTable extends BaseFunction
{
    protected function customizeFunction(): void
    {
        $this->setFunctionPrototype('JSON_TABLE(%s, %s, %s)');
        $this->addNodeMapping('StringPrimary'); // JSON expression
        $this->addNodeMapping('StringPrimary'); // Path expression
        $this->addNodeMapping('StringPrimary'); // COLUMNS definition
    }
}

However, JSON_TABLE's implementation is more complex than this simple approach. In actual SQL usage, the function looks like this:

JSON_TABLE(
  json_expression, 
  path_expression
  COLUMNS (
    column_name data_type PATH path_expression,
    column_name data_type PATH path_expression,
    ...
  )
) AS alias

For a more comprehensive implementation, we would need to:

Handle the COLUMNS clause which requires special parsing
Support the AS alias syntax
Potentially create a more specialized parser for the column definitions

Since JSON_TABLE is a complex SQL construct, we might need to extend beyond the simple function mapping pattern used for other JSON functions. Here are some options:

Implementation using a simple function pattern (as shown above, but may be limited)
Create a specialized parser for JSON_TABLE that handles its complex syntax
Document how users can use JSON_TABLE in native SQL queries instead of DQL

Would you like me to elaborate on any of these approaches? I can also create a more detailed implementation if you prefer a specific direction.

(⌐■_■)

Apr 06 '25 10:04 coderabbitai[bot]

@coderabbitai let's see how a low hanging fruit of documented instructions for native SQL usage will look like.

Apr 06 '25 10:04 martin-georgiev

[!TIP] For best results, initiate chat on the files or code changes.

🧩 Analysis chain

@martin-georgiev Here's a documented approach for using PostgreSQL's JSON_TABLE function with native SQL queries in your library:

## Using PostgreSQL's JSON_TABLE in Native SQL Queries

The `JSON_TABLE` function (available in PostgreSQL 17+) transforms JSON data into a relational format. While not directly supported in DQL functions, you can leverage it in native SQL queries.

### Basic Usage

```php
// Using EntityManager to create a native SQL query
$sql = 'SELECT t.* 
        FROM my_table, 
             JSON_TABLE(my_table.json_data, 
                       \'$\' 
                       COLUMNS (
                           id INT PATH \'$.id\',
                           name VARCHAR PATH \'$.name\',
                           email VARCHAR PATH \'$.contact.email\'
                       )
             ) AS t';

$query = $entityManager->createNativeQuery($sql, $rsm);
$results = $query->getResult();

Setting up ResultSetMapping

use Doctrine\ORM\Query\ResultSetMapping;

$rsm = new ResultSetMapping();
$rsm->addScalarResult('id', 'id', 'integer');
$rsm->addScalarResult('name', 'name', 'string');
$rsm->addScalarResult('email', 'email', 'string');

Practical Example with Array Results

// Data example: records with a JSON column containing user data
$sql = "SELECT u.id as user_id, t.* 
        FROM users u,
             JSON_TABLE(u.profile_data, 
                       '$' 
                       COLUMNS (
                           name VARCHAR PATH '$.name',
                           age INT PATH '$.age',
                           city VARCHAR PATH '$.address.city'
                       )
             ) AS t
        WHERE u.is_active = true";

$rsm = new ResultSetMapping();
$rsm->addScalarResult('user_id', 'userId', 'integer');
$rsm->addScalarResult('name', 'name', 'string');
$rsm->addScalarResult('age', 'age', 'integer');
$rsm->addScalarResult('city', 'city', 'string');

$query = $entityManager->createNativeQuery($sql, $rsm);
$results = $query->getResult();

Handling Multiple Rows from JSON Arrays

// For JSON arrays where each element becomes a row
$sql = "SELECT p.id as product_id, t.* 
        FROM products p,
             JSON_TABLE(p.tags_data, 
                       '$.tags[*]' 
                       COLUMNS (
                           tag_name VARCHAR PATH '$'
                       )
             ) AS t";

$rsm = new ResultSetMapping();
$rsm->addScalarResult('product_id', 'productId', 'integer');
$rsm->addScalarResult('tag_name', 'tagName', 'string');

$query = $entityManager->createNativeQuery($sql, $rsm);
$results = $query->getResult();

Notes

The JSON_TABLE function requires PostgreSQL 17 or higher
String literals within the SQL should be properly escaped
Consider using parameter binding for dynamic values


Would you like me to make any adjustments to this documentation or would you prefer a different approach?

---

🏁 Script executed:

#!/bin/bash

Let's examine the issue to better understand the JSON_TABLE function request

gh issue view 234


Length of output: 294

---

🏁 Script executed:

#!/bin/bash

Let's check the documentation structure and examples in the codebase

find docs -type f | sort cat README.md


Length of output: 4259

---

🌐 Web query:

```web
PostgreSQL 17 JSON_TABLE function syntax and examples

💡 Result:

PostgreSQL 17 introduces the JSON_TABLE() function, enabling SQL queries on JSON data by transforming it into a relational table structure. This feature is particularly useful for extracting nested JSON values, joining JSON with relational tables, and applying SQL operations like filtering or aggregation. Here's a detailed breakdown:

Syntax and Parameters

The basic syntax is:

JSON_TABLE(
  json_doc,          -- JSON/JSONB column or literal
  path_expression    -- JSONPath specifying data to process
  COLUMNS (
    column_name data_type PATH 'json_path' [DEFAULT ... ON ERROR],
    ...
  )
) AS alias_name

json_doc: JSON/JSONB input (e.g., a table column or literal).
path_expression: SQL/JSON path (e.g., '$.fruits[*]' to iterate over a JSON array).
COLUMNS: Defines the output schema:
- column_name: Name of the resulting column.
- data_type: PostgreSQL data type (e.g., VARCHAR, INT).
- PATH: JSONPath to extract the value (e.g., '$.name').
- DEFAULT: Optional fallback value for missing/invalid data[1][2][6].

Key Examples

1. Extracting Nested Arrays

Given a fruit_store table with JSON data:

{
  "category": "Citrus",
  "fruits": [
    {"name": "Orange", "color": "Orange", "price_per_kg": 3.5},
    {"name": "Lemon", "color": "Yellow", "price_per_kg": 4.0}
  ]
}

Extract each fruit as a row:

SELECT * 
FROM fruit_store,
JSON_TABLE(
  json_details, 
  '$.fruits[*]' 
  COLUMNS (
    fruit_name VARCHAR(50) PATH '$.name',
    fruit_color VARCHAR(50) PATH '$.color',
    price_per_kg DECIMAL(5,2) PATH '$.price_per_kg'
  )
) AS fruit_table;

Result:

id	fruit_name	fruit_color	price_per_kg
1	Orange	Orange	3.50
1	Lemon	Yellow	4.00

2. Parsing GeoJSON from a URL

Extract earthquake data from a USGS GeoJSON feed:

WITH http AS (SELECT content FROM http_get('https://earthquake.usgs.gov/...')),
jt AS (
  SELECT * 
  FROM http,
  JSON_TABLE(
    content, 
    '$.features[*]' 
    COLUMNS (
      title TEXT PATH '$.properties.title',
      magnitude REAL PATH '$.properties.mag',
      geometry JSONB PATH '$.geometry'
    )
  )
)
SELECT * FROM jt;

Result:

title	magnitude	geometry
"M 4.0 - Northern Oregon"	4.0	{"type":"Point","coordinates":...}

3. Joining JSON with Relational Data

For a users table with a profile JSONB column:

{"preferences": {"theme": "dark", "newsletter": true}}

Extract preferences:

SELECT name, jt.* 
FROM users,
JSON_TABLE(
  profile, 
  '$.preferences' 
  COLUMNS (
    theme TEXT PATH '$.theme',
    newsletter BOOLEAN PATH '$.newsletter'
  )
) AS jt;

Result:

name	theme	newsletter
Alice Brown	dark	true

Limitations and Tips

No nested columns: Unlike MySQL, PostgreSQL 17’s JSON_TABLE() doesn’t support NESTED PATH for multi-level extraction[8].
Error handling: Use DEFAULT to avoid NULLs for missing fields:
```
price DECIMAL PATH '$.price' DEFAULT 0.0 ON ERROR
```
Performance: Index JSONB columns with GIN indexes for frequent queries[2][3].

JSON_TABLE() bridges relational and semi-structured data, making it easier to integrate JSON APIs or logs into SQL workflows. While simpler than some implementations, it covers most common use cases effectively[2][4][6].

Citations:

1: https://schneide.blog/2024/10/21/json-as-a-table-in-postgresql-17/
2: https://neon.tech/docs/functions/json_table
3: https://www.postgresql.org/docs/current/functions-json.html
4: https://www.crunchydata.com/blog/easily-convert-json-into-columns-and-rows-with-json_table
5: https://www.dbi-services.com/blog/postgresql-17-enhancing-json-support-for-web-developers/
6: https://andyatkinson.com/postgresql-17-json-table-merge-returning-updatable-views
7: https://www.youtube.com/watch?v=es4IGr701RA
8: https://www.percona.com/blog/json_table-will-be-in-postgresql-17/

@martin-georgiev Here's how documented instructions for using PostgreSQL's JSON_TABLE with native SQL queries would look like:

# Using PostgreSQL's JSON_TABLE in Native SQL Queries

## Overview

PostgreSQL 17 introduces the `JSON_TABLE` function which transforms JSON data into a relational format. While not currently implemented as a DQL function in this library, you can use it effectively through Doctrine's native query functionality.

## Basic Syntax

```sql
JSON_TABLE(
  json_document,     -- JSON/JSONB column or literal
  path_expression    -- JSONPath specifying data to process
  COLUMNS (
    column_name data_type PATH json_path [DEFAULT value ON ERROR],
    ...
  )
) AS alias_name

Using with Doctrine Native Queries

Basic Example

use Doctrine\ORM\Query\ResultSetMapping;

// Create ResultSetMapping
$rsm = new ResultSetMapping();
$rsm- >addScalarResult('name', 'name', 'string');
$rsm->addScalarResult('price', 'price', 'float');

// Create native query with JSON_TABLE
$sql = "SELECT jt.* 
        FROM products,
             JSON_TABLE(
                 products.details,
                 '$.items[*]'
                 COLUMNS (
                     name VARCHAR PATH '$.name',
                     price DECIMAL(10,2) PATH '$.price'
                 )
             ) AS jt
        WHERE products.active = true";

$query = $entityManager->createNativeQuery($sql, $rsm);
$results = $query->getResult();

Joining JSON Data with Entity Data

$rsm = new ResultSetMapping();
$rsm->addEntityResult('App\Entity\User', 'u');
$rsm->addFieldResult('u', 'id', 'id');
$rsm->addFieldResult('u', 'email', 'email');
$rsm->addScalarResult('preference', 'preference', 'string');
$rsm->addScalarResult('notification_enabled', 'notificationEnabled', 'boolean');

$sql = "SELECT u.id, u.email, jt.* 
        FROM users u,
             JSON_TABLE(
                 u.profile_data,
                 '$'
                 COLUMNS (
                     preference VARCHAR PATH '$.theme',
                     notification_enabled BOOLEAN PATH '$.notifications.enabled' DEFAULT FALSE ON ERROR
                 )
             ) AS jt
        WHERE u.status = 'active'";

$query = $entityManager->createNativeQuery($sql, $rsm);
$results = $query->getResult();

Processing JSON Arrays into Multiple Rows

$rsm = new ResultSetMapping();
$rsm->addScalarResult('product_id', 'productId', 'integer');
$rsm->addScalarResult('tag', 'tag', 'string');

$sql = "SELECT p.id as product_id, jt.tag
        FROM products p,
             JSON_TABLE(
                 p.tags_data,
                 '$.tags[*]'
                 COLUMNS (
                     tag VARCHAR PATH '$'
                 )
             ) AS jt";

$query = $entityManager->createNativeQuery($sql, $rsm);
$results = $query->getResult();

// This will return multiple rows for each product, one for each tag

Notes and Limitations

JSON_TABLE requires PostgreSQL 17 or higher
Unlike MySQL's implementation, PostgreSQL's JSON_TABLE doesn't support nested paths (NESTED PATH)
For error handling, use the DEFAULT ... ON ERROR syntax to provide fallback values
For optimal performance with frequent JSON queries, consider using GIN indexes on your JSONB columns:
```
CREATE INDEX idx_products_details ON products USING GIN (details);
```

Parameter binding works as expected with native queries:

$sql = "SELECT jt.* FROM products, JSON_TABLE(...) AS jt WHERE products.category = :category";
$query = $entityManager->createNativeQuery($sql, $rsm);
$query->setParameter('category', 'electronics');


Would you like me to add more specific examples or details to this documentation?



<!-- <agentComponentType>chat-answer</agentComponentType> -->

<!-- <rawResChunk><answer><details>
<summary>🧩 Analysis chain</summary>

@martin-georgiev Here's a documented approach for using PostgreSQL's JSON_TABLE function with native SQL queries in your library:

```markdown
## Using PostgreSQL's JSON_TABLE in Native SQL Queries

The `JSON_TABLE` function (available in PostgreSQL 17+) transforms JSON data into a relational format. While not directly supported in DQL functions, you can leverage it in native SQL queries.

### Basic Usage

```php
// Using EntityManager to create a native SQL query
$sql = 'SELECT t.* 
        FROM my_table, 
             JSON_TABLE(my_table.json_data, 
                       \'$\' 
                       COLUMNS (
                           id INT PATH \'$.id\',
                           name VARCHAR PATH \'$.name\',
                           email VARCHAR PATH \'$.contact.email\'
                       )
             ) AS t';

$query = $entityManager->createNativeQuery($sql, $rsm);
$results = $query->getResult();

Setting up ResultSetMapping

use Doctrine\ORM\Query\ResultSetMapping;

$rsm = new ResultSetMapping();
$rsm->addScalarResult('id', 'id', 'integer');
$rsm->addScalarResult('name', 'name', 'string');
$rsm->addScalarResult('email', 'email', 'string');

Practical Example with Array Results

// Data example: records with a JSON column containing user data
$sql = "SELECT u.id as user_id, t.* 
        FROM users u,
             JSON_TABLE(u.profile_data, 
                       '$' 
                       COLUMNS (
                           name VARCHAR PATH '$.name',
                           age INT PATH '$.age',
                           city VARCHAR PATH '$.address.city'
                       )
             ) AS t
        WHERE u.is_active = true";

$rsm = new ResultSetMapping();
$rsm->addScalarResult('user_id', 'userId', 'integer');
$rsm->addScalarResult('name', 'name', 'string');
$rsm->addScalarResult('age', 'age', 'integer');
$rsm->addScalarResult('city', 'city', 'string');

$query = $entityManager->createNativeQuery($sql, $rsm);
$results = $query->getResult();

Handling Multiple Rows from JSON Arrays

// For JSON arrays where each element becomes a row
$sql = "SELECT p.id as product_id, t.* 
        FROM products p,
             JSON_TABLE(p.tags_data, 
                       '$.tags[*]' 
                       COLUMNS (
                           tag_name VARCHAR PATH '$'
                       )
             ) AS t";

$rsm = new ResultSetMapping();
$rsm->addScalarResult('product_id', 'productId', 'integer');
$rsm->addScalarResult('tag_name', 'tagName', 'string');

$query = $entityManager->createNativeQuery($sql, $rsm);
$results = $query->getResult();

Notes

The JSON_TABLE function requires PostgreSQL 17 or higher
String literals within the SQL should be properly escaped
Consider using parameter binding for dynamic values


Would you like me to make any adjustments to this documentation or would you prefer a different approach?

---

🏁 Script executed:

#!/bin/bash

Let's examine the issue to better understand the JSON_TABLE function request

gh issue view 234


Length of output: 294

---

🏁 Script executed:

#!/bin/bash

Let's check the documentation structure and examples in the codebase

find docs -type f | sort cat README.md


Length of output: 4259

---

🌐 Web query:

```web
PostgreSQL 17 JSON_TABLE function syntax and examples

💡 Result:

PostgreSQL 17 introduces the JSON_TABLE() function, enabling SQL queries on JSON data by transforming it into a relational table structure. This feature is particularly useful for extracting nested JSON values, joining JSON with relational tables, and applying SQL operations like filtering or aggregation. Here's a detailed breakdown:

Syntax and Parameters

The basic syntax is:

JSON_TABLE(
  json_doc,          -- JSON/JSONB column or literal
  path_expression    -- JSONPath specifying data to process
  COLUMNS (
    column_name data_type PATH 'json_path' [DEFAULT ... ON ERROR],
    ...
  )
) AS alias_name

json_doc: JSON/JSONB input (e.g., a table column or literal).
path_expression: SQL/JSON path (e.g., '$.fruits[*]' to iterate over a JSON array).
COLUMNS: Defines the output schema:
- column_name: Name of the resulting column.
- data_type: PostgreSQL data type (e.g., VARCHAR, INT).
- PATH: JSONPath to extract the value (e.g., '$.name').
- DEFAULT: Optional fallback value for missing/invalid data[1][2][6].

Key Examples

1. Extracting Nested Arrays

Given a fruit_store table with JSON data:

{
  "category": "Citrus",
  "fruits": [
    {"name": "Orange", "color": "Orange", "price_per_kg": 3.5},
    {"name": "Lemon", "color": "Yellow", "price_per_kg": 4.0}
  ]
}

Extract each fruit as a row:

SELECT * 
FROM fruit_store,
JSON_TABLE(
  json_details, 
  '$.fruits[*]' 
  COLUMNS (
    fruit_name VARCHAR(50) PATH '$.name',
    fruit_color VARCHAR(50) PATH '$.color',
    price_per_kg DECIMAL(5,2) PATH '$.price_per_kg'
  )
) AS fruit_table;

Result:

id	fruit_name	fruit_color	price_per_kg
1	Orange	Orange	3.50
1	Lemon	Yellow	4.00

2. Parsing GeoJSON from a URL

Extract earthquake data from a USGS GeoJSON feed:

WITH http AS (SELECT content FROM http_get('https://earthquake.usgs.gov/...')),
jt AS (
  SELECT * 
  FROM http,
  JSON_TABLE(
    content, 
    '$.features[*]' 
    COLUMNS (
      title TEXT PATH '$.properties.title',
      magnitude REAL PATH '$.properties.mag',
      geometry JSONB PATH '$.geometry'
    )
  )
)
SELECT * FROM jt;

Result:

title	magnitude	geometry
"M 4.0 - Northern Oregon"	4.0	{"type":"Point","coordinates":...}

3. Joining JSON with Relational Data

For a users table with a profile JSONB column:

{"preferences": {"theme": "dark", "newsletter": true}}

Extract preferences:

SELECT name, jt.* 
FROM users,
JSON_TABLE(
  profile, 
  '$.preferences' 
  COLUMNS (
    theme TEXT PATH '$.theme',
    newsletter BOOLEAN PATH '$.newsletter'
  )
) AS jt;

Result:

name	theme	newsletter
Alice Brown	dark	true

Limitations and Tips

No nested columns: Unlike MySQL, PostgreSQL 17’s JSON_TABLE() doesn’t support NESTED PATH for multi-level extraction[8].
Error handling: Use DEFAULT to avoid NULLs for missing fields:
```
price DECIMAL PATH '$.price' DEFAULT 0.0 ON ERROR
```
Performance: Index JSONB columns with GIN indexes for frequent queries[2][3].

JSON_TABLE() bridges relational and semi-structured data, making it easier to integrate JSON APIs or logs into SQL workflows. While simpler than some implementations, it covers most common use cases effectively[2][4][6].

Citations:

1: https://schneide.blog/2024/10/21/json-as-a-table-in-postgresql-17/
2: https://neon.tech/docs/functions/json_table
3: https://www.postgresql.org/docs/current/functions-json.html
4: https://www.crunchydata.com/blog/easily-convert-json-into-columns-and-rows-with-json_table
5: https://www.dbi-services.com/blog/postgresql-17-enhancing-json-support-for-web-developers/
6: https://andyatkinson.com/postgresql-17-json-table-merge-returning-updatable-views
7: https://www.youtube.com/watch?v=es4IGr701RA
8: https://www.percona.com/blog/json_table-will-be-in-postgresql-17/

@martin-georgiev Here's how documented instructions for using PostgreSQL's JSON_TABLE with native SQL queries would look like:

# Using PostgreSQL's JSON_TABLE in Native SQL Queries

## Overview

PostgreSQL 17 introduces the `JSON_TABLE` function which transforms JSON data into a relational format. While not currently implemented as a DQL function in this library, you can use it effectively through Doctrine's native query functionality.

## Basic Syntax

```sql
JSON_TABLE(
  json_document,     -- JSON/JSONB column or literal
  path_expression    -- JSONPath specifying data to process
  COLUMNS (
    column_name data_type PATH json_path [DEFAULT value ON ERROR],
    ...
  )
) AS alias_name

Using with Doctrine Native Queries

Basic Example

use Doctrine\ORM\Query\ResultSetMapping;

// Create ResultSetMapping
$rsm = new ResultSetMapping();
$rsm->addScalarResult('name', 'name', 'string');
$rsm->addScalarResult('price', 'price', 'float');

// Create native query with JSON_TABLE
$sql = "SELECT jt.* 
        FROM products,
             JSON_TABLE(
                 products.details,
                 '$.items[*]'
                 COLUMNS (
                     name VARCHAR PATH '$.name',
                     price DECIMAL(10,2) PATH '$.price'
                 )
             ) AS jt
        WHERE products.active = true";

$query = $entityManager->createNativeQuery($sql, $rsm);
$results = $query->getResult();

Joining JSON Data with Entity Data

$rsm = new ResultSetMapping();
$rsm->addEntityResult('App\Entity\User', 'u');
$rsm->addFieldResult('u', 'id', 'id');
$rsm->addFieldResult('u', 'email', 'email');
$rsm->addScalarResult('preference', 'preference', 'string');
$rsm->addScalarResult('notification_enabled', 'notificationEnabled', 'boolean');

$sql = "SELECT u.id, u.email, jt.* 
        FROM users u,
             JSON_TABLE(
                 u.profile_data,
                 '$'
                 COLUMNS (
                     preference VARCHAR PATH '$.theme',
                     notification_enabled BOOLEAN PATH '$.notifications.enabled' DEFAULT FALSE ON ERROR
                 )
             ) AS jt
        WHERE u.status = 'active'";

$query = $entityManager->createNativeQuery($sql, $rsm);
$results = $query->getResult();

Processing JSON Arrays into Multiple Rows

$rsm = new ResultSetMapping();
$rsm->addScalarResult('product_id', 'productId', 'integer');
$rsm->addScalarResult('tag', 'tag', 'string');

$sql = "SELECT p.id as product_id, jt.tag
        FROM products p,
             JSON_TABLE(
                 p.tags_data,
                 '$.tags[*]'
                 COLUMNS (
                     tag VARCHAR PATH '$'
                 )
             ) AS jt";

$query = $entityManager->createNativeQuery($sql, $rsm);
$results = $query->getResult();

// This will return multiple rows for each product, one for each tag

Notes and Limitations

JSON_TABLE requires PostgreSQL 17 or higher
Unlike MySQL's implementation, PostgreSQL's JSON_TABLE doesn't support nested paths (NESTED PATH)
For error handling, use the DEFAULT ... ON ERROR syntax to provide fallback values
For optimal performance with frequent JSON queries, consider using GIN indexes on your JSONB columns:
```
CREATE INDEX idx_products_details ON products USING GIN (details);
```

Parameter binding works as expected with native queries:

$sql = "SELECT jt.* FROM products, JSON_TABLE(...) AS jt WHERE products.category = :category";
$query = $entityManager->createNativeQuery($sql, $rsm);
$query->setParameter('category', 'electronics');


Would you like me to add more specific examples or details to this documentation?</answer></rawResChunk> -->

Apr 06 '25 10:04 coderabbitai[bot]

postgresql-for-doctrine
postgresql-for-doctrine copied to clipboard

support JSON_TABLE

Setting up ResultSetMapping

Practical Example with Array Results

Handling Multiple Rows from JSON Arrays

Notes

Let's examine the issue to better understand the JSON_TABLE function request

Let's check the documentation structure and examples in the codebase

Syntax and Parameters

Key Examples

1. Extracting Nested Arrays

2. Parsing GeoJSON from a URL

3. Joining JSON with Relational Data

Limitations and Tips

Using with Doctrine Native Queries

Basic Example

Joining JSON Data with Entity Data

Processing JSON Arrays into Multiple Rows

Notes and Limitations

Setting up ResultSetMapping

Practical Example with Array Results

Handling Multiple Rows from JSON Arrays

Notes

Let's examine the issue to better understand the JSON_TABLE function request

Let's check the documentation structure and examples in the codebase

Syntax and Parameters

Key Examples

1. Extracting Nested Arrays

2. Parsing GeoJSON from a URL

3. Joining JSON with Relational Data

Limitations and Tips

Using with Doctrine Native Queries

Basic Example

Joining JSON Data with Entity Data

Processing JSON Arrays into Multiple Rows

Notes and Limitations

postgresql-for-doctrine postgresql-for-doctrine copied to clipboard

support JSON_TABLE

Setting up ResultSetMapping

Practical Example with Array Results

Handling Multiple Rows from JSON Arrays

Notes

Let's examine the issue to better understand the JSON_TABLE function request

Let's check the documentation structure and examples in the codebase

Syntax and Parameters

Key Examples

1. Extracting Nested Arrays

2. Parsing GeoJSON from a URL

3. Joining JSON with Relational Data

Limitations and Tips

Using with Doctrine Native Queries

Basic Example

Joining JSON Data with Entity Data

Processing JSON Arrays into Multiple Rows

Notes and Limitations

Setting up ResultSetMapping

Practical Example with Array Results

Handling Multiple Rows from JSON Arrays

Notes

Let's examine the issue to better understand the JSON_TABLE function request

Let's check the documentation structure and examples in the codebase

Syntax and Parameters

Key Examples

1. Extracting Nested Arrays

2. Parsing GeoJSON from a URL

3. Joining JSON with Relational Data

Limitations and Tips

Using with Doctrine Native Queries

Basic Example

Joining JSON Data with Entity Data

Processing JSON Arrays into Multiple Rows

Notes and Limitations

postgresql-for-doctrine
postgresql-for-doctrine copied to clipboard