Possibility to build query with JSON TABLE #5555

New Issue

admin · 2026-01-22T15:11:12+01:00

admin commented

2026-01-22 15:11:12 +01:00

Originally created by @dgafka on GitHub (May 24, 2017).

Originally assigned to: @Ocramius on GitHub.

Hey,
In oracle database I can make use of JSON TABLE.
In my situation I want to create result record for each object in array (inside json).

So when I will have something like this [{"name":"cleaning"},{"name":"ironing"}] for entry with id 2, when I will do select, I will get 2 records.

The above is just background for the issue.
The sql I need to build in result is something like this:

SELECT OFFER_ID, VALUE                        
           FROM OFFER,   
           JSON_TABLE(o.FEATURES, '$[*]'               
               COLUMNS ( VALUE VARCHAR (100) PATH '$.name')         
           ) as JSON

As you can see there is no concept of join in this sql, neither something like JSON_TABLE exists within database mapping (dynamic table). I know I could solve the problem by creating the sql manually, but I really want to use query builder in here.

So is there any chance I could do it? :)

Originally created by @dgafka on GitHub (May 24, 2017). Originally assigned to: @Ocramius on GitHub. Hey, In `oracle database` I can make use of [JSON TABLE](https://docs.oracle.com/database/121/SQLRF/functions092.htm#SQLRF56973). In my situation I want to create result record for each object in array (inside json). So when I will have something like this `[{"name":"cleaning"},{"name":"ironing"}]` for entry with id 2, when I will do select, I will get 2 records. The above is just background for the issue. The sql I need to build in result is something like this: SELECT OFFER_ID, VALUE FROM OFFER, JSON_TABLE(o.FEATURES, '$[*]' COLUMNS ( VALUE VARCHAR (100) PATH '$.name') ) as JSON As you can see there is `no concept of join` in this sql, `neither something like JSON_TABLE exists within database mapping` (dynamic table). I know I could solve the problem by creating the sql manually, but I really want to use `query builder in here`. So is there any chance I could do it? :)

admin added the Won't Fix Can't Fix Question labels 2026-01-22 15:11:12 +01:00

admin closed this issue

2026-01-22 15:11:12 +01:00

admin commented

2026-01-22 15:11:13 +01:00

@Ocramius commented on GitHub (May 24, 2017):

So is there any chance I could do it? :)

This is where you'd just write SQL instead of DQL.

The feature is both platform specific and very data-transformation related, rather than entity-related. The ORM works on 1:1 data-structures mapped from objects to DB records, so this OracleDB functionality lies outside the boundaries of where doctrine can help you.

@Ocramius commented on GitHub (May 24, 2017): > So is there any chance I could do it? :) This is where you'd just write SQL instead of DQL. The feature is both platform specific and very data-transformation related, rather than entity-related. The ORM works on 1:1 data-structures mapped from objects to DB records, so this OracleDB functionality lies outside the boundaries of where doctrine can help you.

admin commented

2026-01-22 15:11:13 +01:00

@dgafka commented on GitHub (May 24, 2017):

Okey, I understand.

Thanks for the response

@dgafka commented on GitHub (May 24, 2017): Okey, I understand. Thanks for the response

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#5555