Finding JSON element

[Adamhumbug]

Hi,

Normally when populating an editable form with data from the database, i would include an id as a data attribute that i can use to generate a modal with the data to be edited.

I am using JSON.

I know how to find the exact data that i need if i know the path to the JSON but i need to select an element in the JSON that contains something (that would have been the id if normalised).

If i have the following data

{
  "Portal Content": {
    "Colours": {
      "Primary": "f85a40",
      "Secondary": "008374",
      "Default": "222222"
    },
    "Pages": {
      "Home": {
        "Hero": {
          "Hero On": 1,
          "Title": "Welcome to the Accreditation Portal",
          "Paragraph": "This is the home page of the Accreditation Portal.",
          "Hero Cards": {
            "Card 1": {
              "Name": "Card 1",
              "Paragraph": "This is the first card",
              "Icon": 562,
              "Order": 5
            },
            "Card 2": {
              "Name": "Card 2",
              "Paragraph": "This is the second card",
              "Icon": "559",
              "Order": 2
            },
            "Card 3": {
              "Name": "Card 3",
              "Paragraph": "This is the third card",
              "Icon": "560",
              "Order": 3
            },
            "Card 4": {
              "Name": "Card 4",
              "Paragraph": "This is the fourth card",
              "Icon": "561",
              "Order": 4
            }
          }
        }
      },
      "About": {
        "Text": "This is the about page"
      }
    }
  }
}

how would i select card 4 for example, when all i know is its order number.

normally this would be a

select x from y where id = z

i expect that i will need something like

SELECT 
json_extract(portal_content_json, '$.\"Portal Content\".\"Pages\".\"Home\".\"Hero\".\"Hero Cards\"') 

as that is the path to the cards - but selecting the data for the card that i want from here i do not know how to do.  something like JSON_CONTAINS?

 

The other option is to set the data attribute to be "Card 1" - the name of the JSON element rather than the title of the element.

I am currenly setting the data attribute in this function, which would need to be changed if using the "other option"

function getHeroCardsByPortalId($pdo)
{
	$res = $pdo->query("SELECT code, id FROM icon");
	$icons = array_column($res->fetchAll(), 'code', 'id');
	//avoiding the need to join the tables


	$sql = "SELECT 
			json_extract(portal_content_json, '$.\"Portal Content\".\"Pages\".\"Home\".\"Hero\".\"Hero Cards\"') as cards
			FROM portal_content where portal_attachment_id = :portalId";
	$stmt = $pdo->prepare($sql);
	$stmt->execute([
		':portalId' => $_GET['portalId']
	]);

	$data = $stmt->fetch();
	if (!$data) {
		return "<div class='col-12'>No hero cards available!</div>";
	}

	foreach ($data as $cards => $row) {

		$cards = json_decode($row, true);
		$count = sizeof($cards);
		$out = '';
		if ($count == 4) {
			$size = "col-xl-3 col-md-6";
		} else if ($count == 3) {
			$size = "col-xl-4 col-md-4";
		} else if ($count == 2) {
			$size = "col-xl-6 col-md-6";
		} else if ($count == 1) {
			$size = "col-12";
		}

		usort($cards, fn ($a, $b) => $a['Order'] <=> $b['Order']);
		foreach ($cards as $card) {

			$icon_code = $icons[$card['Icon']];
			$out .= "<div class='$size mb-3'>
						<div class='card editHeroCard' data-order-number='$card[Order]'>
							<div class='card-body'>
								<div class='text-center card-title'>
									<i class='$icon_code fa-2xl'></i>
								</div>
								<div class='card-text text-center my-3 fw-bold'>$card[Name]</div>
								<div class='card-text text-center'>$card[Paragraph]</div>
							</div>
						</div>
						<div class='handle text-center'>GRAB</div>
					</div>";
		}
	}
	return $out;
}

 

Edited March 19 by Adamhumbug

[Barand]

https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html

That's where I'd be looking to answer your question so may as well cut out the middle man.

[Danishhafeez]

If you want to select a specific card based on its order number in the JSON structure, you can achieve that by iterating over the cards until you find the one with the desired order number.

function getHeroCardByOrderNumber($pdo, $orderNumber) {
    $sql = "SELECT 
                json_extract(portal_content_json, '$.\"Portal Content\".\"Pages\".\"Home\".\"Hero\".\"Hero Cards\"') as cards
            FROM portal_content where portal_attachment_id = :portalId";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([
        ':portalId' => $_GET['portalId']
    ]);

    $data = $stmt->fetch();
    if (!$data) {
        return null; // or handle the case where no data is found
    }

    $cards = json_decode($data['cards'], true);

    // Find the card with the given order number
    foreach ($cards as $card) {
        if ($card['Order'] == $orderNumber) {
            return $card;
        }
    }

    return null; // or handle the case where the card with the given order number is not found
}

You can then call this function to get the card with the specified order number:

$orderNumber = 4; // Example order number
$card = getHeroCardByOrderNumber($pdo, $orderNumber);
if ($card) {
    // Display or use the card data
    echo "Card Name: " . $card['Name'];
    echo "Card Paragraph: " . $card['Paragraph'];
} else {
    echo "Card not found.";
}

This modified function getHeroCardByOrderNumber will return the card data if found, or null if not found.

Best regard

Danish Hafeez | QA Assistant

ICTInnovations

[Adamhumbug]

16 hours ago, Danishhafeez said:

If you want to select a specific card based on its order number in the JSON structure, you can achieve that by iterating over the cards until you find the one with the desired order number.

function getHeroCardByOrderNumber($pdo, $orderNumber) {
    $sql = "SELECT 
                json_extract(portal_content_json, '$.\"Portal Content\".\"Pages\".\"Home\".\"Hero\".\"Hero Cards\"') as cards
            FROM portal_content where portal_attachment_id = :portalId";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([
        ':portalId' => $_GET['portalId']
    ]);

    $data = $stmt->fetch();
    if (!$data) {
        return null; // or handle the case where no data is found
    }

    $cards = json_decode($data['cards'], true);

    // Find the card with the given order number
    foreach ($cards as $card) {
        if ($card['Order'] == $orderNumber) {
            return $card;
        }
    }

    return null; // or handle the case where the card with the given order number is not found
}

You can then call this function to get the card with the specified order number:

$orderNumber = 4; // Example order number
$card = getHeroCardByOrderNumber($pdo, $orderNumber);
if ($card) {
    // Display or use the card data
    echo "Card Name: " . $card['Name'];
    echo "Card Paragraph: " . $card['Paragraph'];
} else {
    echo "Card not found.";
}

This modified function getHeroCardByOrderNumber will return the card data if found, or null if not found.

Best regard

Danish Hafeez | QA Assistant

ICTInnovations

Thanks for this - what about when it comes to updating the information after it has been displayed.  Again i wont know the exact path so i would have to find the correct element to update.

[Adamhumbug]

On 3/20/2024 at 4:39 AM, Danishhafeez said:

If you want to select a specific card based on its order number in the JSON structure, you can achieve that by iterating over the cards until you find the one with the desired order number.

function getHeroCardByOrderNumber($pdo, $orderNumber) {
    $sql = "SELECT 
                json_extract(portal_content_json, '$.\"Portal Content\".\"Pages\".\"Home\".\"Hero\".\"Hero Cards\"') as cards
            FROM portal_content where portal_attachment_id = :portalId";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([
        ':portalId' => $_GET['portalId']
    ]);

    $data = $stmt->fetch();
    if (!$data) {
        return null; // or handle the case where no data is found
    }

    $cards = json_decode($data['cards'], true);

    // Find the card with the given order number
    foreach ($cards as $card) {
        if ($card['Order'] == $orderNumber) {
            return $card;
        }
    }

    return null; // or handle the case where the card with the given order number is not found
}

You can then call this function to get the card with the specified order number:

$orderNumber = 4; // Example order number
$card = getHeroCardByOrderNumber($pdo, $orderNumber);
if ($card) {
    // Display or use the card data
    echo "Card Name: " . $card['Name'];
    echo "Card Paragraph: " . $card['Paragraph'];
} else {
    echo "Card not found.";
}

This modified function getHeroCardByOrderNumber will return the card data if found, or null if not found.

Best regard

Danish Hafeez | QA Assistant

ICTInnovations

This is great - i can confirm that it did what i needed so thanks for that.  Now i just need to workout how to select by order Number in order to update the values when the data is edited.

[Adamhumbug]

I have been looking at documentation and it seems that something like this is the route that i need to take - but i am getting no results in php my admin when running this query.

SELECT portal_content_json 
from portal_content 
where JSON_SEARCH(
  portal_content_json, 
  'all', 
  2, 
  Null, '
  $[*]."Hero Cards[*]"."Order"') 
  IS NOT NULL;