I am trying to recover the last date of payments inside the itens array (with one or many itens) from the bank_orders json stored inside a SQL Server json_data column.

The query I try returns NULL.

How can I get a single date, being the greater date? And the first date?

JSON structure:

{
    "bank_orders": [
        {
            "operation_number": "123456-abc",
            "itens": [
                {
                    "item_number": 123456,
                    "contract_name": "ACME .inc",
                    "payment_date": "2024-01-03 00:00:00.000",
                    "amount_payment": 3245.21                    
                },

                ...

Query :

SELECT     
    JSON_VALUE(json_data, '$.bank_orders.itens.payment_date') AS last_payment_date
FROM bank_payments

I am trying to recover the last date of payments inside the itens array (with one or many itens) from the bank_orders json stored inside a SQL Server json_data column.

The query I try returns NULL.

How can I get a single date, being the greater date? And the first date?

JSON structure:

{
    "bank_orders": [
        {
            "operation_number": "123456-abc",
            "itens": [
                {
                    "item_number": 123456,
                    "contract_name": "ACME .inc",
                    "payment_date": "2024-01-03 00:00:00.000",
                    "amount_payment": 3245.21                    
                },

                ...

Query :

SELECT     
    JSON_VALUE(json_data, '$.bank_orders.itens.payment_date') AS last_payment_date
FROM bank_payments

• json
• sql-server
• t-sql

• Is the value you need always in the first bank_orders array and first itens (not Items) array? – Thom A Commented 22 hours ago
• Hi @ThomA, Yes! always in the first bank_orders, because there will be only one bank_orders element, inside there will be many itens i wll check if the itens are in order of payment_date – Ângelo Rigo Commented 21 hours ago

2 Answers 2

If you want to shred a JSON array you need OPENJSON. If you only want the first object in bank_orders array then use [0]

SELECT TOP (1) j.*
FROM bank_payments bp
CROSS APPLY OPENJSON(bp.json_data, '$.bank_orders[0].items')
  WITH (
    item_number bigint,
    contract_name nvarchar(1000),
    payment_date datetime2(7),
    amount_payment decimal(19,2)
  ) j
ORDER BY
  j.payment_date DESC;

There are 2 problems with your attempt:

1. Your data is in arrays, but you are treating the parts like it's a big scalar "thing".
2. The inner array is called itens not items.

If, as you state in you comments, the value is always in the first bank_orders and itens array, you can use JSON_VALUE if you specify the array index:

DECLARE @JSON nvarchar(MAX) = N'{
"bank_orders": [
    {
        "operation_number": "123456-abc",
        "itens": [
            {
                "item_number": 123456,
                "contract_name": "ACME .inc",
                "payment_date": "2024-01-03 00:00:00.000",
                "amount_payment": 3245.21                    
            }]
     }]
}';

SELECT JSON_VALUE(@JSON, '$.bank_orders[0].itens[0].payment_date') AS last_payment_date;